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.

 

[dmt32]

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]

You could try something like this:

    Dim AlignLeft As Boolean
    For X = 1 To cNum
    AlingLeft = CBool(X = 1 Or X = 7)
    With WS.Cells(nextrow, X + 1)
        .Value = Me.Controls("txt" & X).Value
        .EntireColumn.AutoFit
        .HorizontalAlignment = IIf(AlignLeft, xlLeft, xlCenter)

Also this code you have added to you don't need to repeat the Application.Match code for each control if the condition being meet is always the same

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

should be able to do something like this:

    Me.Txt7.Visible = Not IsError(Application.Match(cbContactType.Text, Array("Housing Associations", "Landlords"), False))
    Me.mstrAccounts.Visible = Me.Txt7.Visible
    Me.MLA.Visible = Me.Txt7.Visible

You are clearly developing this project as you go along - my first response answered the original request to this thread - Can I suggest that you close this one off and come back to forum with any further help / guidance you may need with your project - This way you likely to get responses from others here as well as myself.

Dave

 

[TheJay]

Hello Dave, thank you for your reply. I will do as you have recommended, but in response to your last post, can you please confirm what I have done wrong? It does not left align the last column.

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
    Dim AlignLeft As Boolean
    For X = 1 To cNum
    AlingLeft = CBool(X = 1 Or X = 7)
    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

Full 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 = Me.txt7.Visible
    Me.MLA.Visible = Me.txt7.Visible
End Sub


Private Sub iptSearch_Click()
      Contacts.Hide
      Unload Contacts
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
    Dim AlignLeft As Boolean
    For X = 1 To cNum
    AlingLeft = CBool(X = 1 Or X = 7)
    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

 

[dmt32]

Hello Dave, thank you for your reply. I will do as you have recommended, but in response to your last post, can you please confirm what I have done wrong? It does not left align the last column.

    Dim AlignLeft As Boolean
    For X = 1 To cNum
    AlingLeft = CBool(X = 1 Or X = 7)
    With ws.Cells(nextrow, X + 1)
        .Value = Me.Controls("txt" & X).Value
        .EntireColumn.AutoFit
        .HorizontalAlignment = IIf([B][COLOR=#ff0000]X = 1[/COLOR][/B], xlLeft, xlCenter)

Look at my post then look at part you have missed updating (shown in RED)


Dave

 

[TheJay]

Ah, like this:

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
        Dim AlignLeft As Boolean
    For X = 1 To cNum
    AlingLeft = CBool(X = 1 Or X = 7)
    With ws.Cells(nextrow, X + 1)
        .Value = Me.Controls("txt" & X).Value
        .EntireColumn.AutoFit
        .HorizontalAlignment = IIf(X = 1 Or X = 7, 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