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
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
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
Try
Set ws = Worksheets(ComboBox1.Value)
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
That is great thank you, i thought that at first but seemed to simple.
as you can see is use
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?ws.Cells(iRow, "B").Value = Application.Max(Worksheets("Projects").Columns("B")) + 1
thanks again ash
Maybe
ws.Cells(iRow, "B").Value = "ABC" & 1000 + Application.Max(Worksheets("Projects").Columns("B")) + 1
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
the issue i have the first part of the reference will change depending on whats selected in the combobox
eg
kitchen - KIT1001
bathroom - BAT1001
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
ive now unchecked in reference as suggested but it put the new code BAYK1001 but doesnt add the 1 to the next line
My bad. Not thinking it out. You can't get a Max of text values.
This will increment on the previous row
Option Explicit goes as the first line in the code module.ws.Cells(iRow, "B").Value = Data & Split(Cells(iRow - 1, "B"), Data)(1) + 1
Tools/Options/Editor tick Auto Syntax Check
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
this returns a runtime error on this line
ws.Cells(iRow, "B").Value = Data & Split(Cells(iRow - 1, "B"), Data)(1) + 1
Maybe
I don't know what's in your workbook. Use Watch and Immediate windows to return values to debug errors.If ws.Cells(iRow, "B").Value = ""
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
Posting Permissions
Reply With Quote