Add worksheets in combobox to enter and save record to selected sheetname automatically

[nagavasarala]

Hi,

I need to maintain my daily sales month wise so have created recommended user form but i have total 12 worksheets in one workbook

so i need to create a combobox in userform and need to Add worksheets in combobox to enter and save record to selected sheetname automatically.

can you help me to figure out the coding.


have given the below but the same is not working.

Private Sub UserForm_Initialize()
For Each sht In ActiveWorkbook.Sheets
Me.ComboBox1.AddItem sht.Name
Next sht


End Sub

please help me

 

[nagavasarala]

 

[Fluff]

The reason the msgbox is not appearing, is that it's not in your code!
Use this

Private Sub CommandButton1_Click()
   Dim currentrow As Long
   Dim Answer
   
   
   With Sheets(ComboBox1.Value)
      currentrow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
      MsgBox .Range("A1").Parent.Name & vbLf & currentrow
      .Cells(currentrow, 1) = TextBox1.Text
      If Application.WorksheetFunction.CountIf(.Range("A2:A" & currentrow), .Cells(currentrow, 1)) > 1 Then
         MsgBox "Duplicate data!", vbCritical, "Remove Data"
         .Cells(currentrow, 1) = ""
      ElseIf Application.WorksheetFunction.CountIf(.Range("A2:A" & currentrow), .Cells(currentrow, 1)) = 1 Then
         Answer = MsgBox("Save the data?", vbYesNo + vbQuestion, "Add Record")
         If Answer = vbYes Then
            .Cells(currentrow, 1) = TextBox1.Text
            .Cells(currentrow, 2) = textbox2.Text
            .Cells(currentrow, 3) = textbox3.Text
            .Cells(currentrow, 4) = ComboBox1.list
            .Cells(currentrow, 5) = ComboBox2.list
            .Cells(currentrow, 6) = ComboBox3.list
            .Cells(currentrow, 7) = TextBox4.Text
            .Cells(currentrow, 8) = TextBox5.Text
            .Cells(currentrow, 9) = TextBox6.Text
            .Cells(currentrow, 10) = TextBox7.Text
            .Cells(currentrow, 11) = TextBox8.Text
            .Cells(currentrow, 12) = TextBox9.Text
            If UserForm1.OptionButton1.Value Then
               .Cells(currentrow, 13) = "BTA"
            Else
               If UserForm1.OptionButton2.Value Then
                  .Cells(currentrow, 13) = "NON-BTA"
               End If
            End If
            .Cells(currentrow, 14) = TextBox10.Text
            .Cells(currentrow, 15) = TextBox11.Value
            .Cells(currentrow, 16) = TextBox12.Value
            .Cells(currentrow, 17) = TextBox13.Value
            .Cells(currentrow, 18) = TextBox14.Value
            .Cells(currentrow, 19) = TextBox15.Value
            .Cells(currentrow, 20) = TextBox16.Value
            .Cells(currentrow, 21) = TextBox17.Value
            .Cells(currentrow, 22) = TextBox18.Value
            .Cells(currentrow, 23) = TextBox19.Value
            .Cells(currentrow, 24) = TextBox20.Value
            .Cells(currentrow, 25) = TextBox21.Value
            .Cells(currentrow, 26) = TextBox22.Value
         End If
      End If
   End With
End Sub

and let me know what the msgbox says.
Also when posting code please use code tags, the # icon in the reply window.

 

[nagavasarala]

thanks bro now it is working and but only one thing the Serial Number is missing it is not in order

Example assume that i have data in already 2 row in Mar18 sheet and if enter data in Apr18 sheet in 3rd row and then immediately if i enter data in Mar18 sheet the serial number is showing 4 after 2 but the row count is correct.

 

[nagavasarala]

also have loaded list of names in combobox2 and 3 and 4. while entering the data the same is showing all the names and able to select particular name but after saving the data it is automatically picking only the first one in the list.

now i'm totally confused why like that and unable to figure out the same.

Please help

 

[Fluff]

Not sure I understand.
Are you saying the the data is now being entered on the correct sheet but on the wrong row?

 

[nagavasarala]

Exactly,

 

[Fluff]

This line

currentrow = .Cells(Rows.Count, 1).End(xlUp).Row + 1

Is using Col A to find the first blank row. Check that you don't have cells with spaces in.
The easiest way is goto to the relevant select a cell in col A below any data then Ctrl & up arrow. Does that take you to the last row of data?