I'm trying to get around VB library/reference issues by using late binding of controls/activex objects on a userform (instead of a worksheet) I cant get my button Click methods to work at all. I've even added a msgbox in its code to see if it gets called & it doesnt.
Any ideas please?
I have a blank userform called 'ufrmUpdateData' & it has this code (so far):
Any ideas please?
I have a blank userform called 'ufrmUpdateData' & it has this code (so far):
Rich (BB code):
'---------------------------------------------------------------------------------------
' Module : UserForm2
' Purpose : USER FORM CODE
'---------------------------------------------------------------------------------------
Private Const MODULE_NAME As String = "UserForm2."
Private Sub btnClose_Click()
MsgBox "clicked close"
'ufrmUpdateData.Hide
Unload Me
End Sub
Rich (BB code):
Private Sub btnUpdateData_Click()
MsgBox "clicked update data now"
End Sub
Private Sub UserForm_Initialize()
'---------------------------------------------------------------------------------------
' Procedure : UserForm_Initialize
' Purpose : Creates controls (labels,combobox,buttons) on fly using
' late binding so uses correct VB library/reference
' DTPicker late binding code per post#5 by timbereng in:
' http://www.access-programmers.co.uk/forums/showthread.php?t=164021
'---------------------------------------------------------------------------------------
' For Error Reporting
Dim sErrorDescr As String
Const sErrSource As String = MODULE_NAME & "UserForm_Initialize"
On Error GoTo Error_In_UserForm_Initialize
' Late binding (As Object) of controls so uses correct VB library/reference
Dim oLblStartDate As Object, oLblEndDate As Object, oLblFilterOn As Object
Dim oCboFilterOn As Object
Dim oBtnUpdateData As Object, oBtnClose As Object
Dim oDtpStartDate As Object, oDtpEndDate As Object
Dim bHasDtPicker As Boolean
' Create control objects
Set oLblStartDate = Me.Controls.Add("Forms.Label.1", "lblStartDate", True)
Set oLblEndDate = Me.Controls.Add("Forms.Label.1", "lblEndDate", True)
Set oLblFilterOn = Me.Controls.Add("Forms.Label.1", "lblFilterOn", True)
Set oCboFilterOn = Me.Controls.Add("Forms.ComboBox.1", "cboFilterOn", True)
Set oBtnUpdateData = Me.Controls.Add("Forms.CommandButton.1", "btnUpdateData", True)
Set oBtnClose = Me.Controls.Add("Forms.CommandButton.1", "btnClose", True)
' Formatting properties
With oLblStartDate
.TabIndex = 0
.Top = 36
.Left = 36
.Height = 22
.Width = 72
.Caption = "Start Date:"
.Font.Size = 12
End With
With oLblEndDate
.TabIndex = 2
.Top = 84
.Left = 36
.Height = 22
.Width = 72
.Caption = "End Date:"
.Font.Size = 12
End With
With oLblFilterOn
.TabIndex = 4
.Top = 132
.Left = 36
.Height = 22
.Width = 72
.Caption = "Filter On:"
.Font.Size = 12
End With
With oCboFilterOn
.TabIndex = 5
.Top = 132
.Left = 120
.Height = 22
.Width = 132
.Font.Size = 12
.AddItem ("CallTime")
.AddItem ("TechComp")
.AddItem ("CloseDate")
.AddItem ("Calc Compl Date")
.ListIndex = 0 ' default value
End With
With oBtnUpdateData
.TabIndex = 6
.Top = 186
.Left = 30
.Height = 36
.Width = 126
.Caption = "Update Data Now"
.Font.Size = 12
End With
With oBtnClose
.TabIndex = 7
.Top = 186
.Left = 198
.Height = 36
.Width = 126
.Caption = "Close"
.Font.Size = 12
End With
'---------------------------
' DTPicker's
'---------------------------
On Error Resume Next
Set oDtpStartDate = Me.Controls.Add("MSComCtl2.DTPicker", "dtpStartDate", True)
Set oDtpEndDate = Me.Controls.Add("MSComCtl2.DTPicker", "dtpEndDate", True)
' If DTPicker doesnt exist, use text boxes instead
If Err.Number <> 0 Or oDtpStartDate Is Nothing Then
On Error GoTo Error_In_UserForm_Initialize
bHasDtPicker = False
Set oDtpStartDate = Me.Controls.Add("Forms.TextBox.1", "dtpStartDate", True)
Set oDtpEndDate = Me.Controls.Add("Forms.TextBox.1", "dtpEndDate", True)
oDtpStartDate.ControlTipText = "Enter date in format: dd mmm yyyy Eg: 01 Apr 2010"
oDtpEndDate.ControlTipText = "Enter date in format: dd mmm yyyy Eg: 31 Mar 2010"
Else
bHasDtPicker = True
oDtpStartDate.Format = dtpLongDate
oDtpEndDate.Format = dtpLongDate
End If
' Formatting properties
With oDtpStartDate
.TabIndex = 1
.Top = 84
.Left = 120
.Height = 22
.Width = 210
.Font.Size = 12
End With
With oDtpEndDate
.TabIndex = 3
.Top = 36
.Left = 120
.Height = 22
.Width = 210
.Font.Size = 12
End With
' ===== Exit Handler =====
Exit_UserForm_Initialize:
oLblStartDate = Nothing
oLblEndDate = Nothing
oLblFilterOn = Nothing
oCboFilterOn = Nothing
oBtnUpdateData = Nothing
oBtnClose = Nothing
oDtpStartDate = Nothing
oDtpEndDate = Nothing
Exit Sub
' ===== ERROR HANDLER =====
Error_In_UserForm_Initialize:
With Err
sErrorDescr = "Error '" & .Number & " " & _
.Description & "' occurred in " & sErrSource & _
IIf(Erl <> 0, " at line " & CStr(Erl) & ".", ".")
End With
Select Case MsgBox(sErrorDescr, vbAbortRetryIgnore, "Error in " & sErrSource)
Case vbRetry
Resume
Case vbIgnore
Resume Next
Case Else
Resume Exit_UserForm_Initialize
End
End Select
End Sub
Last edited:
