PDA

View Full Version : Combo box data to select sheet



ashgull80
05-12-2017, 01:19 AM
Hi
I have a user form with a combo box that has 5 selections, each selection relates to a worksheet, how can i vba code this to input the data on the relevant sheet depending on which is selected from the combo box?

thank you ash

ashgull80
05-12-2017, 01:31 AM
This is my code but i need the worksheet to change depending on whats in the combobox


Private Sub cmdEnter_Click()
Sheets("Projects").Select
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Projects")


'find first empty row in database
iRow = ws.Cells(Rows.Count, 4) _
.End(xlUp).Offset(1, 0).Row


'copy the data to the database
ws.Cells(iRow, "B").Value = Application.Max(Worksheets("Projects").Columns("B")) + 1
ws.Cells(iRow, "C").Value = Me.txtcontactname.Value
ws.Cells(iRow, "D").Value = Me.txtcontactnumber.Value
ws.Cells(iRow, "E").Value = Me.txtcontactemail.Value
ws.Cells(iRow, "F").Value = Me.txtcontactaddress1.Value
ws.Cells(iRow, "G").Value = Me.txtcontactaddress2.Value
ws.Cells(iRow, "H").Value = Me.txtcontactaddress3.Value
ws.Cells(iRow, "I").Value = Me.txtcontactaddress4.Value
ws.Cells(iRow, "J").Value = Me.txtinstalladdress1.Value
ws.Cells(iRow, "K").Value = Me.txtinstalladdress2.Value
ws.Cells(iRow, "L").Value = Me.txtinstalladdress3.Value
ws.Cells(iRow, "M").Value = Me.txtinstalladdress4.Value
ws.Cells(iRow, "N").Value = Me.txtKitRange.Value
'close The Form
Unload Me
End Sub

mdmackillop
05-12-2017, 01:46 AM
Try

Set ws = Worksheets(ComboBox1.Value)

ashgull80
05-12-2017, 01:56 AM
That is great thank you, i thought that at first but seemed to simple.
as you can see is use

ws.Cells(iRow, "B").Value = Application.Max(Worksheets("Projects").Columns("B")) + 1
to create a new job number, the issue i have is id like to start the job number with text eg ABC1001 then id like the next one to be ABC1002 hoew can i do this as my code currently just starts with 1?
thanks again ash

mdmackillop
05-12-2017, 02:08 AM
Maybe

ws.Cells(iRow, "B").Value = "ABC" & 1000 + Application.Max(Worksheets("Projects").Columns("B")) + 1

ashgull80
05-12-2017, 02:49 AM
the issue i have the first part of the reference will change depending on whats selected in the combobox
eg

kitchen - KIT1001
bathroom - BAT1001

mdmackillop
05-12-2017, 03:31 AM
Something like

Select Case combobox1.Value
Case "x"
Data = "ABC"
Case "y"
Data = "DEF"
Case "z"
Data = "GHI"
End Select
ws.Cells(iRow, "B").Value = Data & 1000 + Application.Max(Worksheets("Projects").Columns("B")) + 1

ashgull80
05-12-2017, 03:48 AM
Thank you
this is how my code looks but its showing a compile error, cant find project or library


Private Sub cmdEnter_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets(cboJobType.Value)
'find first empty row in database
iRow = ws.Cells(Rows.Count, 4) _
.End(xlUp).Offset(1, 0).Row

Select Case cboJobType.Value
Case "Bathroom"
Data = "BAYB"
Case "Bedroom"
Data = "BAYBE"
Case "Electrical"
Data = "BAYE"
Case "Installation"
Data = "BAYI"
Case "Kitchen"
Data = "BAYK"
Case "Supply"
Data = "BAYS"

End Select
ws.Cells(iRow, "B").Value = Data & 1000 + Application.Max(Worksheets("Projects").Columns("B")) + 1
'copy the data to the database
ws.Cells(iRow, "B").Value = Application.Max(Worksheets(cboJobType.Value).Columns("B")) + 1
ws.Cells(iRow, "C").Value = Me.txtcontactname.Value
ws.Cells(iRow, "D").Value = Me.txtcontactnumber.Value
ws.Cells(iRow, "E").Value = Me.txtcontactemail.Value
ws.Cells(iRow, "F").Value = Me.txtcontactaddress1.Value
ws.Cells(iRow, "G").Value = Me.txtcontactaddress2.Value
ws.Cells(iRow, "H").Value = Me.txtcontactaddress3.Value
ws.Cells(iRow, "I").Value = Me.txtcontactaddress4.Value
ws.Cells(iRow, "J").Value = Me.txtinstalladdress1.Value
ws.Cells(iRow, "K").Value = Me.txtinstalladdress2.Value
ws.Cells(iRow, "L").Value = Me.txtinstalladdress3.Value
ws.Cells(iRow, "M").Value = Me.txtinstalladdress4.Value


'close The Form
Unload Me
Sheets("Customers").Select
End Sub

mdmackillop
05-12-2017, 04:50 AM
That is usually caused by a missing Reference. Check Tools/References and ubcheck any marked missing.
Also: Use Option Explicit and Declare all your variables
This looks wrong

ws.Cells(iRow, "B").Value = Data & 1000 + Application.Max(Worksheets("Projects").Columns("B")) + 1
'copy the data to the database
ws.Cells(iRow, "B").Value = Application.Max(Worksheets(cboJobType.Value).Columns("B")) + 1

ashgull80
05-12-2017, 05:27 AM
sorry im very basic on this where do i need to use option explicit?
ive changed the code now to this but still the same error

Private Sub cmdEnter_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets(cboJobType.Value)
'find first empty row in database
iRow = ws.Cells(Rows.Count, 4) _
.End(xlUp).Offset(1, 0).Row

Select Case cboJobType.Value
Case "Bathroom"
Data = "BAYB"
Case "Bedroom"
Data = "BAYBE"
Case "Electrical"
Data = "BAYE"
Case "Installation"
Data = "BAYI"
Case "Kitchen"
Data = "BAYK"
Case "Supply"
Data = "BAYS"

End Select


ws.Cells(iRow, "B").Value = Data & 1000 + Application.Max(Worksheets(cboJobType.Value).Columns("B")) + 1
ws.Cells(iRow, "C").Value = Me.txtcontactname.Value
ws.Cells(iRow, "D").Value = Me.txtcontactnumber.Value
ws.Cells(iRow, "E").Value = Me.txtcontactemail.Value
ws.Cells(iRow, "F").Value = Me.txtcontactaddress1.Value
ws.Cells(iRow, "G").Value = Me.txtcontactaddress2.Value
ws.Cells(iRow, "H").Value = Me.txtcontactaddress3.Value
ws.Cells(iRow, "I").Value = Me.txtcontactaddress4.Value
ws.Cells(iRow, "J").Value = Me.txtinstalladdress1.Value
ws.Cells(iRow, "K").Value = Me.txtinstalladdress2.Value
ws.Cells(iRow, "L").Value = Me.txtinstalladdress3.Value
ws.Cells(iRow, "M").Value = Me.txtinstalladdress4.Value


'close The Form
Unload Me
Sheets("Customers").Select
End Sub

ashgull80
05-12-2017, 05:32 AM
ive now unchecked in reference as suggested but it put the new code BAYK1001 but doesnt add the 1 to the next line

mdmackillop
05-12-2017, 07:12 AM
My bad. Not thinking it out. You can't get a Max of text values.
This will increment on the previous row

ws.Cells(iRow, "B").Value = Data & Split(Cells(iRow - 1, "B"), Data)(1) + 1

Option Explicit goes as the first line in the code module.

Tools/Options/Editor tick Auto Syntax Check

ashgull80
05-12-2017, 08:16 AM
That is great thank you very much,
The only issue i have is id like the number to start at 17001 not 1, is this possible?

Thanks again Ash

mdmackillop
05-12-2017, 08:47 AM
ws.Cells(iRow, "B").Value = Data & Split(Cells(iRow - 1, "B"), Data)(1) + 1
If ws.Cells(iRow, "B").Value = Data & 1 Then
ws.Cells(iRow, "B").Value = Data & 17001
End If

ashgull80
05-12-2017, 09:34 AM
this returns a runtime error on this line

ws.Cells(iRow, "B").Value = Data & Split(Cells(iRow - 1, "B"), Data)(1) + 1

mdmackillop
05-12-2017, 10:21 AM
Maybe

If ws.Cells(iRow, "B").Value = ""
I don't know what's in your workbook. Use Watch and Immediate windows to return values to debug errors.

ashgull80
05-13-2017, 03:52 PM
it works fine if theres already a code in column B, so if BAYK17001 is already entered the next line BAYK17002 works fine but if its empty it returns the error.