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.

 

[Norie]

Where do you want the data to go on the sheets?

 

[TheJay]

The next unused row, cells 'A' to 'G'.

 

[TheJay]

This fixes the column problem, but how do I prevent other data already on the worksheet from being replaced?

Dim WS As Worksheet

Private Sub cbContactType_Change()
    With Me.cbContactType
        If .ListIndex <> -1 Then Set WS = Worksheets(.Text)
    End With
End Sub


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


Private Sub cmdbNew_Click()
Dim cNum As Integer
Dim X As Integer
Dim nextrow As Range
Dim sht As String
sht = cbContactType.Value
If Me.cbContactType.Value = "" Then
MsgBox "You must select a 'Contact Type' before you can save the contact details entered."
Exit Sub
End If
cNum = 7
Set nextrow = Sheets(sht).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("txt" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
For X = 1 To cNum
Me.Controls("txt" & X).Value = ""
Next
End Sub


Private Sub cmdbClose_Click()
Unload Me
End Sub

 

[Norie]

As far as I can see that's what this code is doing.

Set nextrow = Sheets(sht).Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)

For X = 1 To cNum
    nextrow = Me.Controls("Reg" & X).Value
    Set nextrow = nextrow.Offset(0, 1)
Next X

Are you sure the data isn't going to the sheet(s)?

Perhaps it's not going to the row you expect it to due to some errant data causing the nextrow to be calculated incorrectly.

 

[TheJay]

Affirmative, it is going into the sheet. It wasn't going to the place I expected it but it is now.

When the data is input, I want the font to be arial size 10. Column A needs to be left aligned horizontally, centred vertically but the (B to G) need to be centre aligned horizontally and vertically.

I presume the code should look something like this?

.EntireColumn.AutoFit
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        With Selection.Font
             .Name = "Arial"
             .FontStyle = "Regular"
             .Size = 10

But how do I stipulate the different formatting for A and B to G?

 

[dmt32]

When the data is input, I want the font to be arial size 10. Column A needs to be left aligned horizontally, centred vertically but the (B to G) need to be centre aligned horizontally and vertically.


But how do I stipulate the different formatting for A and B to G?

Try this update to your codes & see if does what you want:

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 UserForm_Initialize()
    Me.cbContactType.List = Array("Council Contacts", "Local Contacts", "Housing Associations", "Landlords", "Letting & Selling Agents", "Developers")
    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, "A").End(xlUp).Row + 1
    
    cNum = 7
    
    For X = 1 To cNum
    With WS.Cells(nextrow, X)
        .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
'clear control entry
        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

Variable WS must be at TOP of your forms code page outside any procedure.

Dave

 

[TheJay]

Thanks Dave, that's a really good solution preventing the button from being pressed until a contact type is selected.

Since using this new code, I couldn't see the data when I tried to add a new record. I tested it a few times and it seems to ignore the data added and just move down a row. Do you know why? Also, how do I make the first row used for data entry now'B'?

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 & Selling Agents", "Developers")
    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, "A").End(xlUp).Row + 1
    cNum = 7
    For X = 1 To cNum
    With ws.Cells(nextrow, X)
        .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
'clear control entry
    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

 

[dmt32]

Thanks Dave, that's a really good solution preventing the button from being pressed until a contact type is selected.

Since using this new code, I couldn't see the data when I tried to add a new record. I tested it a few times and it seems to ignore the data added and just move down a row. Do you know why?

Code does not activate selected sheet - if you want that just add following shown in RED:

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

Code finds last used cell in Column A adds 1 to variable to place the record to next blank row in range.

nextrow = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row + 1

Also, how do I make the first row used for data entry now'B'?

Do you mean Start From Row 1 Column B?

if so adjust

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

and

With WS.Cells(nextrow, X + 1)

Dave

 

[TheJay]

Hello Dave, thanks for the update.

There is data in each of the worksheets so it should find the next available empty row, it will always be from B to H but the row will depend on where the last entry is found.

Tried the code and am encountering errors.

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

Run-time error ''9': Subscript out of range:

Set ws = Worksheets(cbContactType.Text)

Currently this is the entire 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): ws.Activate
End Sub
Private Sub cmdbUpdate_Click()
End Sub
Private Sub UserForm_Initialize()
    Me.cbContactType.List = Array("Council Contacts", "Local Contacts", "Housing Associations", "Landlords", "Letting & Selling Agents", "Developers")
    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, "A").End(xlUp).Row
    If Len(ws.Cells(1, 1).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
'clear control entry
    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

 

[dmt32]

Error 9 means cannot find the worksheet you selected - check sheet exists & check spelling in your array in UserForm_Initialize event code.

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

Dave