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]

Hi,
You can declare an object variable & assign it to the worksheet object via your combobox change event.

As an example, something like this:

Dim ws As Worksheet
Private Sub ComboBox1_Change()
    With Me.ComboBox1
        If .ListIndex <> -1 Then Set ws = Worksheets(.Text)
    End With
End Sub

Where ws is your object variable this is initialized by use of the Set Statement.
By placing the variable to the top of your forms code page outside any procedure it becomes available to other procedures in the code page.

To populate your combobox you can use the UserForm_Initialize event

Private Sub UserForm_Initialize()
    Me.ComboBox1.List = Array("CC", "LC", "HA", "LL", "LSA")
End Sub

When your form is displayed, your combobox should now contain the worksheet names – each selection you make, the ws object variable will then refer to the selected worksheet.

You can use this in your other codes, navigation, delete etc.

Hope Helpful

Dave

 

[TheJay]

Hello Dave,

Thanks for your reply.

I can insert the code to populate the combobox and that works fine. I can't figure out where or how to insert the other code though?

Dim [COLOR=#ff0000]ws[/COLOR] As Worksheet
Private Sub cbContactType_Change()
    With Me.cbContactType
        If .ListIndex <> -1 Then Set ws = Worksheets(.Text)
    End With
End Sub

 

[TheJay]

So far I have:

Dim ws As WorksheetPrivate 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("[COLOR=#333333]CC", "LC", "HA", "LL", "LSA[/COLOR], "DVPS")
End Sub
Private Sub cmdbClose_Click()
Unload Me
End Sub

It does not populate the data into the text boxes from the worksheet selected in the dropdown though.

 

[dmt32]

So far I have:

Dim ws As WorksheetPrivate 
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("[COLOR=#333333]CC", "LC", "HA", "LL", "LSA[/COLOR], "DVPS")
End Sub
Private Sub cmdbClose_Click()
Unload Me
End Sub

It does not populate the data into the text boxes from the worksheet selected in the dropdown though.

I did state in my post

You can use this in your other codes, navigation, delete etc.

where I assumed you already had other codes to perform required tasks but I take it from your post that you have no other codes for the Userform?

If this is so & you are completely new to userforms, I suggest that you start here:http://www.contextures.com/xlUserForm01.html

Gives a step by step guide on the subject & you can download sample workbook for free.


Hope Helpful

Dave

 

[TheJay]

Hello Dave, thanks for your reply. I have dabbled with userforms and VBA but I just can't get my head around how the combobox code will interact with all the other text boxes and buttons relating to the selected sheet rather than a particular one.

 

[dmt32]

If you do have other codes in your userform then post them here. I & others may be able to offer further guidance. If not, then do have a look at suggested site which may help answer some of your question.

Dave

 

[TheJay]

Hello Dave, I have attempted to implement the code to choose the sheet to add data to and this is what I have:

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 "Select a contact type"
Exit Sub
End If
cNum = 8
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
For X = 1 To cNum
Me.Controls("Reg" & X).Value = ""
Next
End Sub


Private Sub cmdbClose_Click()
Unload Me
End Sub

I am getting an error on:

nextrow = Me.Controls("Reg" & X).Value

'Could not find the specified object'

Do you know what I have done wrong?

 

[Norie]

Do you have controls on the userform named Reg1, Reg2 etc?

 

[TheJay]

Hello Norie, thanks for your reply.

I did, yes. The problem was that I specified 8 when there were only 7 text boxes. Now it seems to run the code without any debug errors but it puts the data into columns 'C' to 'I' rather than 'A' to 'G'. Also, it enters the data into a row where data has already been populated.