Userform Functionality: How to Use a Combobox to Specify Which Worksheet Data is Pulled From/Entered Into

[TheJay]

Hello there, I am designing a userform which enables me to search a workbook with several worksheets containing contact details for different types of organisation.

There are five worksheets that I want to include in the combobox for the 'Update' tab, but there are a number of worksheets which I do not want to include but I also do not want to hide from view outside of the userform.

The worksheets I want to include are called 'CC', 'LC', 'HA', 'LL', 'LSA' and the ones I do not want to include are 'SCH', 'DVP', 'EMP', 'SC', 'WH', 'SS', 'HP'

How do I include these in my combobox so that once one of them is selected, it pulls the data from that worksheet into the userform fields which can be scrolled through using the 'next' and 'prev' buttons, and then a particular entry can be updated, deleted or a new entry can be inserted into that particular worksheet?

I really appreciate your help.

 

[TheJay]

Hello Dave, that's better, thank you, it was a spelling error as I used the '&' symbol instead of the word 'and'.

The problem with the code now is that when I enter data into the userform and click 'new', it replaces whatever I previously had entered in the first row.

Any idea why?

 

[dmt32]

If you are adding your data from Column B onward then these lines will need changing where shown in RED.

     nextrow = WS.Cells(WS.Rows.Count, "B").End(xlUp).Row
    If Len(WS.Cells(1, 2).Value) > 0 Then nextrow = nextrow + 1

Dave

 

[TheJay]

Hello Dave, that works great thanks. I'm not fussed about the userform activating the worksheet that data is being entered to so I removed that code.

The code now:

Dim ws As Worksheet

Private Sub cbContactType_Change()
    Me.cmdbNew.Enabled = CBool(Me.cbContactType.ListIndex <> -1)
    If Me.cbContactType.Enabled Then Set ws = Worksheets(cbContactType.Text)
End Sub


Private Sub cmdbUpdate_Click()


End Sub




Private Sub UserForm_Initialize()
    Me.cbContactType.List = Array("Council Contacts", "Local Contacts", "Housing Associations", "Landlords", "Letting and Selling Agents", "Developers", "Employers")
    Me.cmdbNew.Enabled = False
End Sub


Private Sub cmdbNew_Click()
Dim cNum As Integer, X As Integer
    Dim nextrow As Long
    nextrow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    If Len(ws.Cells(1, 2).Value) > 0 Then nextrow = nextrow + 1
    cNum = 7
    For X = 1 To cNum
    With ws.Cells(nextrow, X + 1)
        .Value = Me.Controls("txt" & X).Value
        .EntireColumn.AutoFit
        .HorizontalAlignment = IIf(X = 1, xlLeft, xlCenter)
        .VerticalAlignment = xlCenter
        With .Font
             .Name = "Arial"
             .FontStyle = "Regular"
             .Size = 10
        End With
    End With
    Me.Controls("txt" & X).Text = ""
    Next
    MsgBox ws.Name & Chr(10) & "Record Added", 48, "Record Added"
End Sub


Private Sub cmdbClose_Click()
Unload Me
End Sub

I have added a multiline text box to the userform and I only want it to be enabled on one or two particular worksheets, how do I enable this functionality using the combobox?

Also, how do I change the confirmation message so it says, 'Data added to 'Worksheet''?

 

[TheJay]

An image example will help:

As per the previous description, but with pre-existing text that further text is added alongside when it is enabled.

So
'Example1:
Example2:
Example3:' go into the cell, followed by text input by the user.

 

[dmt32]

I have added a multiline text box to the userform and I only want it to be enabled on one or two particular worksheets, how do I enable this functionality using the combobox?

Also, how do I change the confirmation message so it says, 'Data added to 'Worksheet''?

Try

Private Sub cbContactType_Change()
    Me.cmdbNew.Enabled = CBool(Me.cbContactType.ListIndex <> -1)
    If Me.cbContactType.Enabled Then Set WS = Worksheets(cbContactType.Text): WS.Activate
    Me.TxtExample.Enabled = Not IsError(Application.Match(cbContactType.Text, Array("Council Contacts", "Local Contacts"), False))
End Sub

You will need to change the values sown in RED as required.
An alternative suggestion would be to use the controls visible property (change Enabled to Visible) so control is only visible when required.

Replace MsgBox line with following:

MsgBox WS.Name & Chr(10) & "Data added to 'Worksheet.", 48, "Data Added"

Dave

 

[TheJay]

Thanks Dave, that works really well.

Can you please explain a couple of things to me? Firstly, I can see the code the dictates the cells should be centre aligned but I can't see what dictates the first column is left aligned? If I can understand this then perhaps I can figure out how to left align the data that goes into the worksheet from the last textbox.

Secondly, how does the code work which greys out the 'new' button? Can the text in the last example I showed also be greyed out when the form is first loaded up and can I add code to stop that text from being cleared from the userform when an entry is added to the workbook? Also, how do I avoid the example text from being input into one of the worksheets not specified by default? If it should not be an option for a worksheet, that text should not go into the row.

I'm not sure what I need to do to customise the confirmation message about data being entered, I like the way that it says what worksheet the data has gone into but it doesn't make sense, it reads: 'Local Contacts Data Added' when it should read 'Data Added to Local Contacts'. Obviously the worksheet name will depend on what was selected from the combobox.

I would be interested to see the suggestion you mentioned in action, hiding the example text box on worksheets where it does not apply.

Lastly, the 'new' button should not be enabled until at least one text box has data entered.

Dim ws As Worksheet

Private Sub cbContactType_Change()
    Me.cmdbNew.Enabled = CBool(Me.cbContactType.ListIndex <> -1)
    If Me.cbContactType.Enabled Then Set ws = Worksheets(cbContactType.Text)
    Me.txt7.Enabled = Not IsError(Application.Match(cbContactType.Text, Array("Housing Associations", "Landlords"), False))
End Sub


Private Sub UserForm_Initialize()
    Me.cbContactType.List = Array("Council Contacts", "Local Contacts", "Housing Associations", "Landlords", "Letting and Selling Agents", "Developers", "Employers")
    Me.cmdbNew.Enabled = False
    Me.txt7.Enabled = False
End Sub


Private Sub cmdbNew_Click()
Dim cNum As Integer, X As Integer
    Dim nextrow As Long
    nextrow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    If Len(ws.Cells(1, 2).Value) > 0 Then nextrow = nextrow + 1
    cNum = 7
    For X = 1 To cNum
    With ws.Cells(nextrow, X + 1)
        .Value = Me.Controls("txt" & X).Value
        .EntireColumn.AutoFit
        .HorizontalAlignment = IIf(X = 1, xlLeft, xlCenter)
        .VerticalAlignment = xlCenter
        With .Font
             .Name = "Arial"
             .FontStyle = "Regular"
             .Size = 10
        End With
    End With
    Me.Controls("txt" & X).Text = ""
    Next
    MsgBox ws.Name & Chr(10) & "Data added.", 48, "Data Added"
End Sub


Private Sub cmdbClose_Click()
Unload Me
End Sub

 

[TheJay]

Changed icon in MsgBox:

[COLOR=#333333]    MsgBox ws.Name & Chr(10) & "Data added.", 64, "Data Added"[/COLOR]

 

[TheJay]

I am also trying to implement a row toggle and row number generator which isn't working for some reason.

Dim ws As Worksheet

Private Sub cbContactType_Change()
    Me.cmdbNew.Enabled = CBool(Me.cbContactType.ListIndex <> -1)
    If Me.cbContactType.Enabled Then Set ws = Worksheets(cbContactType.Text)
    Me.txt7.Enabled = Not IsError(Application.Match(cbContactType.Text, Array("Housing Associations", "Landlords"), False))
End Sub


Private Sub cmdbChange_SpinUp()
    If Me.cbContactType.ListRows.Count < 1 Then Exit Sub
    If CurrentRow > 1 Then
        CurrentRow = CurrentRow - 1
        UpdatecmdbChange
    End If
End Sub


Private Sub cmdbChange_SpinDown()
    If CurrentRow = Me.cbContactType.ListRows.Count Then Exit Sub
    If CurrentRow < Me.cbContactType.ListRows.Count Then
        CurrentRow = CurrentRow + 1
        UpdatecmdbChange
    End If
End Sub


Private Sub UpdatePositionCaption()
    dtaRow.Caption = CurrentRow & " of " & Me.cbContactType.ListRows.Count
End Sub


Private Sub UserForm_Initialize()
    Me.cbContactType.List = Array("Council Contacts", "Local Contacts", "Housing Associations", "Landlords", "Letting and Selling Agents", "Developers", "Employers")
    Me.cmdbNew.Enabled = False
    Me.txt7.Enabled = False
End Sub


Private Sub cmdbNew_Click()
Dim cNum As Integer, X As Integer
    Dim nextrow As Long
    nextrow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    If Len(ws.Cells(1, 2).Value) > 0 Then nextrow = nextrow + 1
    cNum = 7
    For X = 1 To cNum
    With ws.Cells(nextrow, X + 1)
        .Value = Me.Controls("txt" & X).Value
        .EntireColumn.AutoFit
        .HorizontalAlignment = IIf(X = 1, xlLeft, xlCenter)
        .VerticalAlignment = xlCenter
        With .Font
             .Name = "Arial"
             .FontStyle = "Regular"
             .Size = 10
        End With
    End With
    Me.Controls("txt" & X).Text = ""
    Next
    MsgBox ws.Name & Chr(10) & "Data added.", 64, "Success"
End Sub


Private Sub cmdbClose_Click()
Unload Me
End Sub

 

[dmt32]

Thanks Dave, that works really well.

Can you please explain a couple of things to me? Firstly, I can see the code the dictates the cells should be centre aligned but I can't see what dictates the first column is left aligned? If I can understand this then perhaps I can figure out how to left align the data that goes into the worksheet from the last textbox.

This line of code manages alignment

.HorizontalAlignment = IIf(X = 1, xlLeft, xlCenter)

where X in your For Next Loop = 1 Alignment is xlLeft All othe cells are xlCentre

Secondly, how does the code work which greys out the 'new' button?

This line

Me.cmdbNew.Enabled = CBool(Me.cbContactType.ListIndex <> -1)

returns True if ComboBox Listindex <> - 1 (clear) otherwise False

I'm not sure what I need to do to customise the confirmation message about data being entered, I like the way that it says what worksheet the data has gone into but it doesn't make sense, it reads: 'Local Contacts Data Added' when it should read 'Data Added to Local Contacts'. Obviously the worksheet name will depend on what was selected from the combobox.

Try this

  MsgBox "Data added to " & WS.Name, 48, "Data Added"

48 is the value for constant vbExclamation Icon & vbOKOnly button is displayed.

I would be interested to see the suggestion you mentioned in action, hiding the example text box on worksheets where it does not apply.

Just change the Enabled property to Visible

Me.Txt7.Visible =

Dave

 

[TheJay]

That works great, thank you.

Although I now understand how that code for alignment works, I am not sure how I make row 7 also align the same as row 1?

[COLOR=#333333].HorizontalAlignment = IIf([/COLOR][COLOR=#ff0000]X[/COLOR][COLOR=#333333] = 1, xlLeft, xlCenter)[/COLOR]

I have updated the code:

Dim ws As Worksheet

Private Sub cbContactType_Change()
    Me.cmdbNew.Enabled = CBool(Me.cbContactType.ListIndex <> -1)
    If Me.cbContactType.Enabled Then Set ws = Worksheets(cbContactType.Text)
    Me.txt7.Visible = Not IsError(Application.Match(cbContactType.Text, Array("Housing Associations", "Landlords"), False))
    Me.mstrAccounts.Visible = Not IsError(Application.Match(cbContactType.Text, Array("Housing Associations", "Landlords"), False))
    Me.MLA.Visible = Not IsError(Application.Match(cbContactType.Text, Array("Housing Associations", "Landlords"), False))
End Sub


'Private Sub cmdbChange_SpinUp()
'    If Me.cbContactType.ListRows.Count < 1 Then Exit Sub
'    If CurrentRow > 1 Then
'        CurrentRow = CurrentRow - 1
'        UpdatecmdbChange
'    End If
'End Sub


'Private Sub cmdbChange_SpinDown()
'    If CurrentRow = Me.cbContactType.ListRows.Count Then Exit Sub
'    If CurrentRow < Me.cbContactType.ListRows.Count Then
'        CurrentRow = CurrentRow + 1
'        UpdatecmdbChange
'    End If
'End Sub


'Private Sub UpdatePositionCaption()
'    dtaRow.Caption = CurrentRow & " of " & Me.cbContactType.ListRows.Count
'End Sub


Private Sub UserForm_Initialize()
    Me.cbContactType.List = Array("Council Contacts", "Local Contacts", "Housing Associations", "Landlords", "Letting and Selling Agents", "Developers", "Employers")
    Me.cmdbNew.Enabled = False
    Me.txt7.Visible = False
    Me.mstrAccounts.Visible = False
    Me.MLA.Visible = False
End Sub


Private Sub cmdbNew_Click()
Dim cNum As Integer, X As Integer
    Dim nextrow As Long
    nextrow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    If Len(ws.Cells(1, 2).Value) > 0 Then nextrow = nextrow + 1
    cNum = 7
    For X = 1 To cNum
    With ws.Cells(nextrow, X + 1)
        .Value = Me.Controls("txt" & X).Value
        .EntireColumn.AutoFit
        .HorizontalAlignment = IIf(X = 1, xlLeft, xlCenter)
        .VerticalAlignment = xlCenter
        With .Font
             .Name = "Arial"
             .FontStyle = "Regular"
             .Size = 10
        End With
    End With
    Me.Controls("txt" & X).Text = ""
    Next
    MsgBox "Contact added to " & ws.Name, 64, "Contact Added"
End Sub


Private Sub cmdbClose_Click()
Unload Me
End Sub

I have also added two option buttons to the form 'MLA' called 'mstrYes' and 'mstrNo'. 'mstrNo' should be the default and I want to prevent the text box 'txt7' from appearing until mstrYes is selected, and if mstrNo is selected again, the text box should disappear again.

Also in relation to the text box 'txt7', how do I prevent the text within 'txt7' that appears automatically in 'Example1, Example2, Example3' mentioned previously from being cleared during the following procedure?

Me.Controls("txt" & X).Text = ""