Worksheets problem

[sale]

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]

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.

[sale]

i just need simple code example which will put 10 or more worksheets in one combobox

[Bob Phillips]

With Me.Combobox1

For Each sh In Activeworkbook.Worksheets

.AddItem sh.Name
Next sh
End With

[sale]

ty man that helped me a lot

[sale]

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]

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

[sale]

works for me also, ty a lot

[sale]

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

[sale]

can some1 tell me where is problem plz

[mdmackillop]

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.

[sale]

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]

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.

[sale]

hm, i mean code works fine for first 30 or 35 sheets and then after that error pops out

[Paul_Hossler]

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

[sale]

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]

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.

[sale]

ok ty all for help