VBA-Programmierung in Microsoft Excel

Tutorial: Excel-Beispiele

Kopfzeilen anhand von Max/Min-Werten ermitteln

Gruppe

Funktion

Bereich

INDEX

Thema

Kopfzeilen anhand von Max/Min-Werten ermitteln

Problem

Zu den Werten im Bereich F2:I2 werden im Bereich F4:I4 die Kopfzeilen aus der Matrix in A1:D2 geliefert.

Lösung

Darstellung nur anhand einer Beispielarbeitsmappe möglich.




ClassModule: frmCaption

Private Type RECT
        Left As Long
        Top As Long
        Right As Long
        Bottom As Long
End Type

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
                ByVal lpClassName As String, _
                ByVal lpWindowName As String) As Long

Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
              (ByVal hWnd As Long, ByVal nIndex As Long) As Long
                
Private Declare Function SetWindowLong _
               Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, _
               ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Private Declare Function DrawMenuBar Lib "user32" ( _
               ByVal hWnd As Long) As Long

Private Sub cmdCancel_Click()
  Unload Me
End Sub

Private Sub cmdOn_Click()
  Call fncHasUserformCaption(True)
End Sub

Private Sub cmdOff_Click()
   Call fncHasUserformCaption(False)
End Sub

Private Sub UserForm_Initialize()
  Call fncHasUserformCaption(False)
End Sub

Private Function fncHasUserformCaption(bState As Boolean)
  Dim Userform_hWnd As Long
  Dim Userform_Style As Long
  Dim Userform_Rect As RECT
  Const GWL_STYLE = (-16)
  Const WS_CAPTION = &HC00000
  Userform_hWnd = FindWindow( _
     lpClassName:=IIf(Val(Application.Version) > 8, _
     "ThunderDFrame", "ThunderXFrame"), _
     lpWindowName:=Me.Caption)
  Userform_Style = GetWindowLong(hWnd:=Userform_hWnd, _
     nIndex:=GWL_STYLE)
  If bState = True Then
     Userform_Style = Userform_Style Or WS_CAPTION
  Else
     Userform_Style = Userform_Style And Not WS_CAPTION
  End If
  Call SetWindowLong(hWnd:=Userform_hWnd, nIndex:=GWL_STYLE, _
     dwNewLong:=Userform_Style)
  Call DrawMenuBar(hWnd:=Userform_hWnd)
End Function

StandardModule: Modul1

Sub DialogAufruf()
   frmCaption.Show
End Sub

    


Beiträge aus dem Excel-Forum zu den Themen Funktion und INDEX