excel 2010 userforms vba

Question

I have an issue with a Userform in Excel 2010. Here is the code:

Private Sub Label1_Click()

End Sub

Private Sub Userform_Initialize()

'EmptyRoleTextbox
RoleTextBox.Value = ""

'EmptyNameTextBox1
NameTextBox1.Value = ""

'EmptyDirTextbox1
DirTextBox1.Value = ""

'EmptyRemarksTextBox1
RemarksTextBox1.Value = ""

'EmptyAWSComboBox
AWSComboBox.Clear

'FillAWSComboBox
With AWSComboBox
    .AddItem "MA"
    .AddItem "Combat"
    .AddItem "Cbt Sp"
    .AddItem "CSS"
    .AddItem "Comd Sp"
    .AddItem "AM"
    .AddItem "Medical"
    .AddItem "Military Police"
    .AddItem "Baker Street"
    .AddItem "Putney"
    .AddItem "Sloane Square"
    .AddItem "Kings Cross"
    .AddItem "Whitechapel"
    .AddItem "Holland Park"
End With


End Sub

Private Sub Add_Click()

Dim emptyRow As Long

Dim LastRow As Long

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

ActiveSheet.Cells(LastRow + 1, "A").Value = ActiveSheet.Cells(LastRow, "A").Value + 1


'Transfer information
If AWSComboBox.Value = "MA" Then
Sheet1.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A"))
LastRow = WorksheetFunction.CountA(Range("A:A"))
Cells(emptyRow, 2).Value = RoleTextBox.Value
Cells(emptyRow, 3).Value = NameTextBox1.Value
Cells(emptyRow, 4).Value = DirTextBox1.Value
Cells(emptyRow, 5).Value = RemarksTextBox1.Value
ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select

ElseIf AWSComboBox.Value = "Combat" Then
Sheet2.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A"))
LastRow = WorksheetFunction.CountA(Range("A:A"))
Cells(emptyRow, 2).Value = RoleTextBox.Value
Cells(emptyRow, 3).Value = NameTextBox1.Value
Cells(emptyRow, 4).Value = DirTextBox1.Value
Cells(emptyRow, 5).Value = RemarksTextBox1.Value
ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select

ElseIf AWSComboBox.Value = "Cbt Sp" Then
Sheet3.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A"))
LastRow = WorksheetFunction.CountA(Range("A:A"))
Cells(emptyRow, 2).Value = RoleTextBox.Value
Cells(emptyRow, 3).Value = NameTextBox1.Value
Cells(emptyRow, 4).Value = DirTextBox1.Value
Cells(emptyRow, 5).Value = RemarksTextBox1.Value
ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select

ElseIf AWSComboBox.Value = "CSS" Then
Sheet4.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A"))
LastRow = WorksheetFunction.CountA(Range("A:A"))
Cells(emptyRow, 2).Value = RoleTextBox.Value
Cells(emptyRow, 3).Value = NameTextBox1.Value
Cells(emptyRow, 4).Value = DirTextBox1.Value
Cells(emptyRow, 5).Value = RemarksTextBox1.Value
ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select

ElseIf AWSComboBox.Value = "Comd Sp" Then
Sheet5.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A"))
LastRow = WorksheetFunction.CountA(Range("A:A"))
Cells(emptyRow, 2).Value = RoleTextBox.Value
Cells(emptyRow, 3).Value = NameTextBox1.Value
Cells(emptyRow, 4).Value = DirTextBox1.Value
Cells(emptyRow, 5).Value = RemarksTextBox1.Value
ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select

ElseIf AWSComboBox.Value = "AM" Then
Sheet6.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A"))
LastRow = WorksheetFunction.CountA(Range("A:A"))
Cells(emptyRow, 2).Value = RoleTextBox.Value
Cells(emptyRow, 3).Value = NameTextBox1.Value
Cells(emptyRow, 4).Value = DirTextBox1.Value
Cells(emptyRow, 5).Value = RemarksTextBox1.Value
ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select

ElseIf AWSComboBox.Value = "Medical" Then
Sheet7.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A"))
LastRow = WorksheetFunction.CountA(Range("A:A"))
Cells(emptyRow, 2).Value = RoleTextBox.Value
Cells(emptyRow, 3).Value = NameTextBox1.Value
Cells(emptyRow, 4).Value = DirTextBox1.Value
Cells(emptyRow, 5).Value = RemarksTextBox1.Value
ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select

ElseIf AWSComboBox.Value = "Military Police" Then
Sheet8.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A"))
LastRow = WorksheetFunction.CountA(Range("A:A"))
Cells(emptyRow, 2).Value = RoleTextBox.Value
Cells(emptyRow, 3).Value = NameTextBox1.Value
Cells(emptyRow, 4).Value = DirTextBox1.Value
Cells(emptyRow, 5).Value = RemarksTextBox1.Value
ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select

ElseIf AWSComboBox.Value = "Baker Street" Then
Sheet9.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A"))
LastRow = WorksheetFunction.CountA(Range("A:A"))
Cells(emptyRow, 2).Value = RoleTextBox.Value
Cells(emptyRow, 3).Value = NameTextBox1.Value
Cells(emptyRow, 4).Value = DirTextBox1.Value
Cells(emptyRow, 5).Value = RemarksTextBox1.Value
ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select

ElseIf AWSComboBox.Value = "Putney" Then
Sheet10.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A"))
LastRow = WorksheetFunction.CountA(Range("A:A"))
Cells(emptyRow, 2).Value = RoleTextBox.Value
Cells(emptyRow, 3).Value = NameTextBox1.Value
Cells(emptyRow, 4).Value = DirTextBox1.Value
Cells(emptyRow, 5).Value = RemarksTextBox1.Value
ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select

ElseIf AWSComboBox.Value = "Sloane Square" Then
Sheet11.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A"))
LastRow = WorksheetFunction.CountA(Range("A:A"))
Cells(emptyRow, 2).Value = RoleTextBox.Value
Cells(emptyRow, 3).Value = NameTextBox1.Value
Cells(emptyRow, 4).Value = DirTextBox1.Value
Cells(emptyRow, 5).Value = RemarksTextBox1.Value
ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select

ElseIf AWSComboBox.Value = "Kings Cross" Then
Sheet12.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A"))
LastRow = WorksheetFunction.CountA(Range("A:A"))
Cells(emptyRow, 2).Value = RoleTextBox.Value
Cells(emptyRow, 3).Value = NameTextBox1.Value
Cells(emptyRow, 4).Value = DirTextBox1.Value
Cells(emptyRow, 5).Value = RemarksTextBox1.Value
ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select

ElseIf AWSComboBox.Value = "Whitechapel" Then
Sheet13.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A"))
LastRow = WorksheetFunction.CountA(Range("A:A"))
Cells(emptyRow, 2).Value = RoleTextBox.Value
Cells(emptyRow, 3).Value = NameTextBox1.Value
Cells(emptyRow, 4).Value = DirTextBox1.Value
Cells(emptyRow, 5).Value = RemarksTextBox1.Value
ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select

ElseIf AWSComboBox.Value = "Holland Park" Then
Sheet14.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A"))
LastRow = WorksheetFunction.CountA(Range("A:A"))
Cells(emptyRow, 2).Value = RoleTextBox.Value
Cells(emptyRow, 3).Value = NameTextBox1.Value
Cells(emptyRow, 4).Value = DirTextBox1.Value
Cells(emptyRow, 5).Value = RemarksTextBox1.Value
ActiveSheet.Cells(ActiveSheet.Rows.Count, Selection.Column).End(xlUp).Select

End If

Unload Me

End Sub

Private Sub Cancel_Click()

    enter code here

Unload Me

End Sub

Basically each item in the combo box is a separate worksheet. When I enter information into the box, I want it to go in to the correct worksheet in the empty row. But column A in all sheets is the Serial numbers of the items, so the part about the LastRow states to at an additional number in that column automatically.

Now, my issues is, if I am selecting a different worksheet (i.e "MA") but I fill in details to go to the "Cbt" sheet, it will overwrite the last row in that sheet. However if I am already selecting the right sheet, then it works no problem.

I have a feeling it is to do with the ActiveSheet. No idea how to fix it.

Always use the Range and Cells with Owner sheet object, i.e. Sheet1.Range("A:A"), Sheet1.Cells(emptyRow, 2). — smozgur, May 16 '16 at 09:25
Or with ActiveSheet since you already activate the sheet in each section ActiveSheet.Range("A:A"), ActiveSheet.Cells(emptyRow, 2). Side comment: your code could be shortened A LOT. — smozgur, May 16 '16 at 09:28
Declare and directly work on the objects you want to automate. [Avoid using Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — L42, May 16 '16 at 09:48
referencing workbook, sheet , range/cell is basic knowledge in excel. Next time make your sample code shorter pls. — Patrick Lepelletier, May 17 '16 at 09:39

score 0 · Answer 1 · answered May 16 '16 at 09:49

Instead of using ActiveSheet (because as you are experiencing, it could change from what you want it to be), create a reference to the required worksheet and work on that. Example below: -

Private Sub MyWorkSheet()
Dim WkSht As Excel.Worksheet

set WkSht = ThisWorkbook.WorkSheets(Me.AWSComboBox.Value)
    LastRow = WkSht.Range("A" & Rows.Count).End(xlUp).Row
Set WkSht = Nothing

End Sub

excel 2010 userforms vba

1 Answers1