Search code examples
excelvbacomboboxreferenceuserform

How can I make my code change the worksheet it is referencing depending on the value in a combobox?

I have a bit of a head scratcher here. I am a very inexperienced VBA user, and I am making a Sales entry form. Right now, I have a combo box that is populating with the names of different sheets on the workbook. This is a picture of my form so far

I have "next" and "previous" buttons that pull up info from different rows using this code:

Private Sub BHSDNEXTTAPBUTTONLF_Click()
With Me.BHSDROWLABELLF
If Not IsEmpty(Worksheets("GG TAPS").Cells(.Caption + 2, 20)) Then
.Caption = Val(.Caption) + 1
End If

Me.BHSDADDRESSLF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 23)
Me.BHSDALTPHONELF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 26)
Me.BHSDCAMPAIGNSLF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 34)
Me.BHSDCCPDLF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 31)
Me.BHSDCOMPANYNAMELF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 21)
Me.BHSDCSZLF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 24)
Me.BHSDCVVLF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 4)
Me.BHSDEMAILLF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 22)
Me.BHSDEXPLF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 3)
Me.BHSDHIGHAMOUNTLF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 25)
Me.BHSDHOWWHOLF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 32)
Me.BHSDLASTCARDLF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 2)
Me.BHSDMAINNUMBERLF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 19)
Me.BHSDPOSS1LF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 27)
Me.BHSDPOSS2LF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 28)
Me.BHSDPOSS3LF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 29)
Me.BHSDTAPNAMELF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 20)
Me.BHSDWHATWHYLF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 33)
Me.BHSDZIPCODELF.Value = Worksheets("GG TAPS").Cells(.Caption + 1, 5)

End With
End Sub

Instead of Worksheets("GG TAPS").Cells, I want the worksheet to be the name that is currently selected in the combo box. Is this possible?

Solution

  • Work with Objects. Your code will become much simpler to handle.

    Is this what you are trying? (UNTESTED)

    I have commented the code. But if you face an issue then leave a comment below.

    Option Explicit

Sub Sample()
    Dim ws As Worksheet
    
    '~~> Check if the user has selected a value in Combo box
    '~~> Change ComboBox1 with the relevant combo box
    If ComboBox1.ListIndex = -1 Then
        MsgBox "Select the name of the worksheet"
        Exit Sub
    End If
    
    '~~> This is to handle any typo while populating the combo
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(ComboBox1.Value)
    On Error GoTo 0
    
    If ws Is Nothing Then
        MsgBox "This workbook doesn't have the sheet with the name " & ComboBox1.Value
        Exit Sub
    End If
    
    '~~> And then you can use the worksheet Object
    If Not IsEmpty(ws.Cells(BHSDROWLABELLF.Caption + 2, 20).Value2) Then
        BHSDROWLABELLF.Caption = Val(BHSDROWLABELLF.Caption) + 1
    End If
    
    '~~> OR using WITH - END WITH
    
    With ws
        If Not IsEmpty(.Cells(BHSDROWLABELLF.Caption + 2, 20).Value2) Then
            BHSDROWLABELLF.Caption = Val(BHSDROWLABELLF.Caption) + 1
        End If
    End With
    
    '
    '~~> Rest of the code
    '
End Sub