excel vba: condensing with select case

[jedwardo]

Hi Everyone, I'm trying to simplify this code but am not sure how to go about it or where to put it. I thought maybe a select case in userform_click event but am not having any luck.

These all just call an on screen keyboard when textbox is selected and set the variants for returning the data from the keyboard to the userform textboxes.

Private Sub TextBoxAddress_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxAddressS_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxCellPhone_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxCellPhoneS_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxCity_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxCityS_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxEmail_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxEmailS_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxFirstName_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxFirstNameS_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxHomePhone_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxHomePhoneS_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxLastName_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxLastNameS_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxSecondEmail_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxSecondEmailS_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxState_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxStateS_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxWorkPhone_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxWorkPhoneS_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxZip_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub


Private Sub TextBoxZipS_Enter()
    tPar = ActiveControl.Parent
    tField = ActiveControl.Name
    UFKeyboard.Show
End Sub

It's working just fine and I'd leave it as is but there are a lot more textboxes to add into the mix once I find a solution and the code page is already quite long.

Thanks, Jordan

 

[Rick Rothstein]

Will ALL TextBoxes have this same Enter event code?

 

[jedwardo]

Yes sir, all the same

 

[jedwardo]

I was toying around with this code to try and group them together. Unfortunately it works fine for a Change event but does not let me modify it as an Enter event to call the keyboard. Do you know if there's something similar that can be done with Enter events for a collection?

On Userform:

Private m_colTBoxes As Collection


Private Sub UserForm_Initialize()


    Dim lngIndex As Long
    Dim clsTBox As Class1
    
    Set m_colTBoxes = New Collection
    For lngIndex = 1 To 3
        Set clsTBox = New Class1
        Set clsTBox.TBox = Me.Controls("Textbox" & lngIndex)
        m_colTBoxes.Add clsTBox, CStr(m_colTBoxes.Count + 1)
    Next
    
End Sub

And this in the Class Modules:

Public WithEvents TBox As MSForms.TextBox


Private Sub TBox_Change()
    MsgBox "Validate " & Me.TBox.Name
End Sub

 

[Rick Rothstein]

I was toying around with this code to try and group them together. Unfortunately it works fine for a Change event but does not let me modify it as an Enter event to call the keyboard. Do you know if there's something similar that can be done with Enter events for a collection?

That was what I had in mind when I asked you about the functionality applying to all TextBoxes... and I too found that the Enter event could not be Class'ed, which I really do not understand as it is a normal, legitimate event for a TextBox.

 

[jedwardo]

No problem, thanks for looking into it. It's functioning fine I'm just having fun with it now.