VBA Excel

[ANWAR78]

Hello everyone, I am trying to create a Voucher posting system, so i can post accounting transactions easily, but there is something wrong with my coding, can any one help me in this regards, I have never learn about Coding.

Private Sub CommandButton1_Click()

Dim emptyRow As Long


'Make Sheet2 active
Sheet2.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 2).Value = TextBox1.Value
Cells(emptyRow, 1).Value = TextBox2.Value
Cells(emptyRow, 3).Value = TextBox25.Value
Cells(emptyRow, 4).Value = ComboBox1.Value
Cells(emptyRow, 5).Value = ComboBox2.Value
Cells(emptyRow, 6).Value = TextBox3.Value
Cells(emptyRow, 7).Value = TextBox13.Value

Sheet1.Activate
Unload Me


End Sub

 

[Anthony47]

Use On Error Resume Next to understand which line of your code fails. Also, I shouldn't change sheet selection having a userfor open, but recommend to fully address the sheet in the single rows of the macro; we can do that using With
Finally I should use a different method for checking the first available row, in case that your data dont' start on row1 or you have any empty row inside your data.

That said, try:

Private Sub CommandButton1_Click()

'Make Sheet2 active
''Sheet2.Activate

'Determine emptyRow
''emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
emptyRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1

'Transfer information
With Sheets("Sheet2")
    On Error Resume Next
    .Cells(emptyRow, 2).Value = TextBox1.Value
    .Cells(emptyRow, 1).Value = TextBox2.Value
    .Cells(emptyRow, 3).Value = TextBox25.Value
    .Cells(emptyRow, 4).Value = ComboBox1.Value
    .Cells(emptyRow, 5).Value = ComboBox2.Value
    .Cells(emptyRow, 6).Value = TextBox3.Value
    .Cells(emptyRow, 7).Value = TextBox13.Value
    On Error GoTo 0
End With
''Sheet1.Activate
Unload Me

End Sub

You need to investigate the code that should fill the cell left empty or not changed after CommandButton is clicked

 

[mohadin]

In addition to what Anthony47 suggested
Don't you need to show Userform1 before transforming the data form TextBox's to the sheet?!

Private Sub CommandButton1_Click()
    Dim emptyRow As Long
    Userform1.Show
    'Make Sheet2 active
    'Sheet2.Activate
    With Sheet2
        'Determine emptyRow
        emptyRow = WorksheetFunction.CountA(.Range("A:A")) + 1
        'Transfer information
        .Cells(emptyRow, 2).Value = TextBox1.Value
        .Cells(emptyRow, 1).Value = TextBox2.Value
        .Cells(emptyRow, 3).Value = TextBox25.Value
        .Cells(emptyRow, 4).Value = ComboBox1.Value
        .Cells(emptyRow, 5).Value = ComboBox2.Value
        .Cells(emptyRow, 6).Value = TextBox3.Value
        .Cells(emptyRow, 7).Value = TextBox13.Value
    End With
    Unload Me
End Sub

Private Sub CommandButton1_Click()

'Make Sheet2 active
''Sheet2.Activate

'Determine emptyRow
''emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Userform1.Show
'Transfer information
With Sheets("Sheet2")
    emptyRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    On Error Resume Next
    .Cells(emptyRow, 2).Value = TextBox1.Value
    .Cells(emptyRow, 1).Value = TextBox2.Value
    .Cells(emptyRow, 3).Value = TextBox25.Value
    .Cells(emptyRow, 4).Value = ComboBox1.Value
    .Cells(emptyRow, 5).Value = ComboBox2.Value
    .Cells(emptyRow, 6).Value = TextBox3.Value
    .Cells(emptyRow, 7).Value = TextBox13.Value
    On Error GoTo 0
End With
''Sheet1.Activate
Unload Me

End Sub

 

[Anthony47]

@mohadin
Hello; I guess the Sub CommandButton1_Click is the handler of the button captioned POST /SAVE on the OP's userform; otherwise the several TextBox.. /ComboBox.. have to be fully addressed: Userform1.TextBox... /Userform1.ComboBox..

 

[mohadin]

But, Have you seen the PIC at the very top of the thread?

 

[Anthony47]

But, Have you seen the PIC at the very top of the thread?

Yes, and my guess is that CommandButton1 is the button captioned POST /SAVE
Let's wait for ANWAR's reply and see what else he might need after our suggestions. Have a nice day

 

[Fluff]

Use On Error Resume Next to understand which line of your code fails.

How is that going to help? All it will do is mask the error.

 

[Anthony47]

My original message say:

You need to investigate the code that should fill the cell left empty or not changed after CommandButton is clicked

 

[Fluff]

Oops, didn't see that at the bottom of the post, but IMO telling people to use On Error Resume Next in that manner is a very bad idea.

 

[mohadin]