CODEWITHSUNDEEP
vbaexcelms-office
Zach Roberts
Zach Roberts

Reputation: 11

Excel userform combo box select sheet to put values in

Hi i am trying to program a user form in excel that a user inputs the information and can select the worksheet that they want the information that was entered in to go to that spread sheet.

This is what i have so far. 

Dim iRow As Long
Dim sheet As String

sheet = ComboBox1.Value
Worksheets(sheet).Activate

iRow = sheet.Cells.Find(what:="*", seatchOrder:=xlRows, searchdirection:=xlPrevious, LookIn:=xlValues).Row + 1

when i run the user form and select the worksheet in the combo box and hit the command button to run the form i get the error "invalid qualifier"

it highlights sheet.cells

here is the entire code if it helps:

    Private Sub ComboBox1_Change()

End Sub

Private Sub CommandButton1_Click()
'get item button

Dim sheet As String
UserForm8.Hide

MsgBox ("Select an item to update")

sheet = ComboBox1.Value
Worksheets(sheet).Activate

Set ProjRng = Application.InputBox(Message, Title, "", 377, 58, , , 8)
    ProjSel = ProjRng.Cells.Row


Label1.Enabled = True
Label2.Enabled = True
Label3.Enabled = True
Label4.Enabled = True
Label8.Enabled = True
Label10.Enabled = True
TextBox1.Enabled = True
TextBox2.Enabled = True
TextBox3.Enabled = True
TextBox4.Enabled = True
TextBox8.Enabled = True
TextBox10.Enabled = True
TextBox10.Locked = False
CommandButton1.Enabled = True
ComboBox1.Enabled = True





UserForm8.TextBox1.Value = ActiveSheet.Cells(ProjSel, 1).Value
UserForm8.TextBox2.Value = ActiveSheet.Cells(ProjSel, 2).Value
UserForm8.TextBox3.Value = ActiveSheet.Cells(ProjSel, 3).Value
UserForm8.TextBox4.Value = ActiveSheet.Cells(ProjSel, 4).Value
UserForm8.TextBox8.Value = ActiveSheet.Cells(ProjSel, 8).Value
UserForm8.TextBox11.Value = ActiveSheet.Cells(ProjSel, 6).Value
    UserForm8.Show



End Sub

Private Sub CommandButton2_Click()
'Update button to update the remaing quantity amount

Dim iRow As Long
Dim sheet As String

sheet = ComboBox1.Value
Worksheets(sheet).Activate

iRow = sheet.Cells.Find(what:="*", seatchOrder:=xlRows, searchdirection:=xlPrevious, LookIn:=xlValues).Row + 1

With Worksheets("ChemLog")
    .Cells(iRow, 6).Value = Me.TextBox12
End With


With sheet
    .Cells(iRow, 1).Value = Me.TextBox1.Value


end with    

    'continue above with columns according to log








End Sub






Private Sub TextBox10_Change()


End Sub

Private Sub TextBox11_Change()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
ComboBox1.AddItem ("Standards")
ComboBox1.AddItem ("Acids,Bases, and Buffers")
ComboBox1.AddItem ("Solvents and Flammables")

End Sub

Upvotes: 1

Views: 9834

Answers (1)

Andy G
Andy G

Reputation: 19367

As well as the spelling error, sheet is a string, so it requires:

Worksheets(sheet).Cells.Find(..

which is the reason for the specific error message you receive.

Upvotes: 1

Related Questions