View Full Version : Worksheets problem
Hi, i need to make some litlle app in excel for my mom.
I have multiple sheets, they look all the same but they need to be filled with diferent data. I made one form for entering the data into sheets, but i want one combobox where i can choose speciffic sheet where the data from form needs to be placed.
For example :
1st text box : first name
2nd text box : last name
3rd text box : phone
and sometimes i want that data to put in sheet1, sometimes in sheet2, so i want one combo box in that form where i can chose sheet where that data will be stored.
this is simple example, i have over 50 sheets i my example.
ty for any help
Bob Phillips
01-19-2012, 05:22 AM
That is simple enough, but what have you done to date, how far did you get, what specific problems do you have? We seeks to assist, but not do the project for you.
i just need simple code example which will put 10 or more worksheets in one combobox
Bob Phillips
01-19-2012, 08:16 AM
With Me.Combobox1
For Each sh In Activeworkbook.Worksheets
.AddItem sh.Name
Next sh
End With
ty man that helped me a lot
As you can see in code below I have one more question. I will have over 50 sheets and as you can see if I will do it like I did below that will be a huge code. Its not a problem to make this for 3 sheets but for over 50 shets it is.
Private Sub UserForm_Initialize()
Dim sh As Worksheet
With Me.ComboBox1
For Each sh In ActiveWorkbook.Worksheets
.AddItem sh.Name
Next sh
End With
End Sub
Private Sub btnEnter_Click()
Dim row1, row2, row3 As Long
Dim wsCard1, wsCard2, wsCard3 As Worksheet
Set wsCard1 = Worksheets("Sheet1")
Set wsCard2 = Worksheets("Sheet2")
Set wsCard3 = Worksheets("Sheet3")
'Finding the first empty row in specific sheet
row1 = wsCard1.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
row2 = wsCard2.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
row3 = wsCard3.Cells(Rows.Count, 1) _
.End(xlUp).Offset(2, 0).Row
Select Case Me.ComboBox1.Value
Case "Sheet1"
wsCard1.Cells(row1, 1).Value = txtDate.Value
wsCard1.Cells(row1, 2).Value = Me.ComboBoxDoc.Value
wsCard1.Cells(row1, 3).Value = Me.txtNumber.Value
wsCard1.Cells(row1, 4).Value = Me.txtEvent.Value
wsCard1.Cells(row1, 5).Value = Me.txtDue.Value
wsCard1.Cells(row1, 6).Value = Me.txtBill.Value
Case "Sheet2"
wsCard2.Cells(row2, 1).Value = txtDate.Value
wsCard2.Cells(row2, 2).Value = Me.ComboBoxDoc.Value
wsCard2.Cells(row2, 3).Value = Me.txtNumber.Value
wsCard2.Cells(row2, 4).Value = Me.txtEvent.Value
wsCard2.Cells(row2, 5).Value = Me.txtDue.Value
wsCard2.Cells(row2, 6).Value = Me.txtBill.Value
Case "Sheet3"
wsCard3.Cells(row3, 1).Value = txtDate.Value
wsCard3.Cells(row3, 2).Value = Me.ComboBoxDoc.Value
wsCard3.Cells(row3, 3).Value = Me.txtNumber.Value
wsCard3.Cells(row3, 4).Value = Me.txtEvent.Value
wsCard3.Cells(row3, 5).Value = Me.txtDue.Value
wsCard3.Cells(row3, 6).Value = Me.txtBill.Value
End Select
End Sub
I tried to make one block of code for all sheets but it doesnt work, can some1 tell me where i did wrong
Dim row As Long
Set row = Worksheets(Me.ComboBox1.Value)
Dim wsCard As Worksheet
Set wsCard = Worksheets(Me.ComboBox1.Value)
row = wsCard.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
wsCard.Cells(row, 1).Value = txtDatum1.Value
wsCard.Cells(row, 2).Value = Me.ComboBoxDoc1.Value
wsCard.Cells(row, 3).Value = Me.txtNumber.Value
wsCard.Cells(row, 4).Value = Me.txtEvent.Value
wsCard.Cells(row, 5).Value = Me.txtDue.Value
wsCard.Cells(row, 6).Value = Me.txtBill.Value
End Sub
ty for help
mancubus
01-19-2012, 03:36 PM
worked for me...
1 to 6 is for testing...
Dim ws As Worksheet
Dim LastRow As Long
Set ws = Worksheets(ComboBox1.Value)
With ws
LastRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(LastRow, 1).Value = 1 'txtDatum1.Value
.Cells(LastRow, 2).Value = 2 'Me.ComboBoxDoc1.Value
.Cells(LastRow, 3).Value = 3 'Me.txtNumber.Value
.Cells(LastRow, 4).Value = 4 'Me.txtEvent.Value
.Cells(LastRow, 5).Value = 5 'Me.txtDue.Value
.Cells(LastRow, 6).Value = 6 'Me.txtBill.Value
End With
works for me also, ty a lot
everything works great, actually everything works great for first 30 or so sheets, when i chose 40th or 50th sheet in combo box and when i try to put data in sheets with button an error pops out (run-time error '9' : subscript out of range
my code is :
Dim ws As Worksheet
Dim LastRow As Long
Set ws = Worksheets(ComboBox1.Value)
With ws
LastRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(LastRow, 1).Value = txtDatum1.Value
.Cells(LastRow, 2).Value = Me.ComboBoxIsprava1.Value
.Cells(LastRow, 3).Value = Me.txtBroj1.Value
.Cells(LastRow, 4).Value = Me.txtPosProm1.Value
.Cells(LastRow, 5).Value = Me.txtDuguje1.Value
.Cells(LastRow, 6).Value = Me.txtPotrazuje1.Value
End With
Me.ComboBoxIsprava1 = ""
Me.txtBroj1 = ""
Me.txtPosProm1 = ""
Me.txtDuguje1 = ""
Me.txtPotrazuje1 = ""
Me.ComboBox1 = ""
Me.ComboBox1.SetFocus
can some1 tell me where is problem plz
mdmackillop
01-21-2012, 04:07 PM
What are the sheet names that fail? Is the name exactly the same, or maybe trimmed if numerical?
Try
On Error Resume Next
Set ws = Worksheets(ComboBox1.Value)
If Err<>0 then MsgBox ComboBox1.Value
On Error GoTo 0
'etc.
I changed code now like u said but still getting an error
Dim ws As Worksheet
Dim LastRow As Long
On Error Resume Next
Set ws = Worksheets(ComboBox1.Value)
If Err <> 0 Then MsgBox ComboBox1.Value
On Error GoTo 0
With ws
LastRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(LastRow, 1).Value = txtDatum1.Value
.Cells(LastRow, 2).Value = Me.ComboBoxIsprava1.Value
.Cells(LastRow, 3).Value = Me.txtBroj1.Value
.Cells(LastRow, 4).Value = Me.txtPosProm1.Value
.Cells(LastRow, 5).Value = Me.txtDuguje1.Value
.Cells(LastRow, 6).Value = Me.txtPotrazuje1.Value
End With
Sheet name are different, I need full names of sheets in combo box list but the sheet nemase needs to longer than 31 characters so in combo box list i dont use sheet name, i use specific cell in every sheet
Dim sh As Worksheet
With Me.ComboBox1
For Each sh In ActiveWorkbook.Worksheets
.AddItem sh.Range("D1").Value
Next sh
End With
mdmackillop
01-22-2012, 05:17 AM
If your sheet names are not exactly the same as in your combobox, your code will fail. Without knowing the names, we can't suggest a workaround.
hm, i mean code works fine for first 30 or 35 sheets and then after that error pops out
Paul_Hossler
01-22-2012, 06:45 AM
If the failing sheet keeps moving around (30, 35, etc.) it might be that the order of the sheets is different between runs
not tested
Dim sh As Worksheet
With Me.ComboBox1
For Each sh In ActiveWorkbook.Worksheets
If ucase(sh.Range("D1").Value) <> ucase(sh.name) then
Msgbox "This will fail " & sh.name
else
.AddItem sh.Range("D1").Value
end if
Next sh
End With
Paul
with this code i get only sheets that dont fail in cobmo box, but is there any posible way to get all sheets in combo box and also works.
code works normally for all sheets when i use
For Each sh In ActiveWorkbook.Worksheets
.AddItem sh.Name
Next sh
but when i use this code code fails after first 30 or so sheets
For Each sh In ActiveWorkbook.Worksheets
.AddItem sh.Range("D1").Value
Next sh
mdmackillop
01-22-2012, 10:52 AM
is there any posible way to get all sheets in combo box and also works
Not without knowing the names of all the worksheets as previously stated.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.