Refer to a sheet selected by combobox

[TimBrVa]

hi, when the workbook launches, a multipage userform auto populates. page 1 (0) is a combobox where the user can select which month of data they want to either view from or add to, along with an option to add a new month. where im struggling is in subsequent page. For instance page 2 (1) the "add customer page" i have a group of textboxes and combo boxes that when the command button "add" is selected will write the fields to the appropriate rows/cells. however, i keep getting a 'subscript out of range ' error.. thanks in advance

 

[TimBrVa]

Heres the code for better viewing..

Sub CommandButton1_Click()
On Error Resume Next
Set ss = ThisWorkbook.Sheets(ComboBox33.Value)
If Me.ComboBox33.Value = "Add New Sheet" Then
Sheets("Blank for New Tab").Copy After:=Sheets(Sheets.Count)
Sheets("Blank for New Tab(2)").name = NewSheetNametxt.Value
Me.ComboBox33.AddItem NewSheetNametxt.Value
Sheets(NewSheetNametxt.Value).Select
MultiPage1.Value = 1
Else
ss.Select
End If
MultiPage1.Value = 1
End Sub

Private Sub AddCustcmd_Click()
Dim ss As Worksheet
Set ss = ThisWorkbook.Sheets(ComboBox33.Value)

Dim lr As Integer
lr = Application.WorksheetFunction.CountA(ss.Range("A:A"))

Me.Custcmb.Text = ss.Range("A" & lr).Value 'Company
Me.Contactlbl.Text = ss.Range("B" & lr).Value 'Contact
Me.Citycmb.Text = ss.Range("C" & lr).Value 'city
Me.Statecmb.Text = ss.Range("D" & lr).Value 'state
Me.Leadcmb.Text = ss.Range("E" & lr).Value 'ANA Lead
Me.Unitcmb.Text = ss.Range("F" & lr).Value 'Unit
Me.Quantitylbl.Text = ss.Range("G" & lr).Value 'Quantity
Me.Pricelbl.Text = ss.Range("H" & lr).Value 'Price
Me.Fleetlbl.Text = ss.Range("J" & lr).Value 'Fleet / Rental
Me.Marketlbl.Text = ss.Range("K" & lr).Value 'Market Channel
Me.Probabilitylbl1.Text = ss.Range("L" & lr).Value 'Probability
Me.Statuscmb.Text = ss.Range("M" & lr).Value 'Status
Me.Days_closelbl1.Text = ss.Range("N" & lr).Value 'Days to Close
End Sub

 

[dmt32]

Hi,
untested & a complete guess but see if this update to your code helps you

Sub CommandButton1_Click()
    Dim NewSheetName    As String
    Dim AddNewSheet     As Boolean
    
    NewSheetName = Me.NewSheetNametxt.Value
    
    With Me.combobox3
        If Len(.Value) = 0 Then Exit Sub
        
        AddNewSheet = .Value = "Add New Sheet"
        
        If Not AddNewSheet Then
            
            Worksheets(.Value).Activate
            
        ElseIf Len(NewSheetName) > 0 Then
        
            Worksheets("Blank For New Tab").Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = NewSheetName
            
            .AddItem NewSheetName
            .ListIndex = .ListCount - 1
            
        End If
    End With
    
    MultiPage1.Value = 1
End Sub

If issue still not resolved then suggest to give forum better understanding of your project that you place a copy of your workbook with dummy data in a file sharing site like dropbox & provide a link to it.

Dave