1

I have created a tool using Excel to gather inputs from a user and use it to do some processing of data. I have created a UI on a worksheet with a bunch of ActiveX controls (TextBox, ListBox, ComboBox). Part of the ActiveX controls are dynamic - they are added at run time based on "metadata" that the tool admin creates on a second worksheet. Metadata contains the field name, type of ActiveX control, position of the control, ListRange to populate values, Multi-Text/Multi-Select flag, etc.

I am able to successfully add the ActiveX controls to the UI worksheet. However, now I want to add functionality for ActiveX TextBox controls to show a default text, when the control gets focus - default text gets removed, when the control loses focus - if user has entered any data it remains otherwise the default text shows up again.

 Public Sub df_segment_GotFocus()
   Dim wb As Workbook
   Set wb = ThisWorkbook
   Set form_sheet = Worksheets(Sheet1.Name)

   If form_sheet.OLEObjects("df_segment") Is Nothing Then

   Else
    'When user begins to type, remove the help text and remove Italics
    Dim seg_val As String
    seg_val = form_sheet.OLEObjects("df_segment").Object.Value
    If seg_val = "e.g. Desktop-Mac,Desktop-Win,Mobile-OSX" Then
          form_sheet.OLEObjects("df_segment").Object.Font.Italic = False
          form_sheet.OLEObjects("df_segment").Object.Value = ""
    Else
          form_sheet.OLEObjects("df_segment").Object.Value = seg_val
    End If
   End If
End Sub

Public Sub df_segment_LostFocus()
   Dim wb As Workbook
   Set wb = ThisWorkbook
   Set form_sheet = Worksheets(Sheet1.Name)

   If form_sheet.OLEObjects("df_segment") Is Nothing Then

   Else
    'Incase user doesn't enter any values, show the help text again
    Dim seg_val As String
    seg_val = form_sheet.OLEObjects("df_segment").Object.Value
    If seg_val = "" Then
          form_sheet.OLEObjects("df_segment").Object.Font.Italic = True
          form_sheet.OLEObjects("df_segment").Object.Value = "e.g. Desktop-Mac,Desktop-Win,Mobile-OSX"
    Else
          form_sheet.OLEObjects("df_segment").Object.Value = seg_val
    End If
   End If
End Sub

In the sample code above, you can see that I am using the exact name of the control to setup the GotFocus and LostFocus event handlers. However, since my UI is metadata driven, the controls will be added/removed dynamically and I wouldn't know the name of the controls to explicitly add the event handlers.

I looked up the forums and implemented this: a.) Implemented a Class Module

Public WithEvents df_TextBox As MSForms.TextBox
Public df_TextBox_Name As String

Private Sub df_TextBox_Change()
     Dim wb As Workbook
     Set wb = ThisWorkbook
     Set form_sheet = Worksheets(Sheet1.Name)
     Set metadata_sheet = Worksheets(Sheet2.Name)

     Dim obj_name As String
     obj_name = df_TextBox_Name
     obj_val = form_sheet.OLEObjects(obj_name).Object.Value

     MsgBox "Change in TextBox" & obj_val

End Sub

b.) Created objects for the Class where I instantiate the control objects

  ElseIf d_Type = "TextBox" Then
     df_obj.Object.Value = d_def_val
     df_obj.Object.Font.Italic = True

     If d_Multi = 1 Then
        df_obj.Object.MultiLine = True
     End If

     '--------------------------------------------------------------
     'part where we add the custom events for GotFocus and LostFocus
     '--------------------------------------------------------------
     ReDim Preserve TextBox_Event_Array(1 To i)
     Set TextBox_Event_Array(i).df_TextBox = df_obj.Object
     TextBox_Event_Array(i).df_TextBox_Name = df_obj.Name

Problem Statements

1.) When I create the class module, I don't see the GotFocus and LostFocus events available. Only Change, KeyDown/Press/Up, MouseDown/Move/Up

2.) I created a Change event handler just to test the Class Module but I do not see it getting triggered.

Any suggestions on how can I fix the problem or any alternate solutions?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • Any help on this? – Anoorag Saxena Sep 21 '18 at 18:52
  • 1) Got and Lostfocus won't work with withevents (as they're events of the container-control) (I am trying to 'catch' them with a connecttoconnectionpoint api, but still no luck) 2) Have a look here: https://stackoverflow.com/questions/1083603/vba-using-withevents-on-userforms – EvR Sep 24 '18 at 08:01

0 Answers0