User Form VBA code

[Willmonbo]

I am new to VBA code, and with limited knowledge i try to construct some VBA code for a simple user form to worksheet data input.

so I have a few text boxes that the user enters data into, and then the VBA code pastes the data onto the worksheet row by row, meaning that every time data is entered in the user form, my code has to look for the last row of data then offset one row and paste the data there and for some reason my code always pastes the data on the same row (in the 4th row).

can someone please take a look at the code and figure out what I'm doing wrong?

here is the code:

Dim NextAvailable as long

NextAvailable = ClubLevelMove

Sheets("database").Select

Cells(NextAvailable, 11) = YearTXTBX.Text
Cells(NextAvailable, 12) = ChairmensTXTBX.Text
Cells(NextAvailable, 13) = PresidentsTXTBX.Text
Cells(NextAvailable, 14) = ExecutiveTXTBX.Text
Cells(NextAvailable, 15) = LeadersClubTXTBX.Text

end sub

Function ClubLevelMove()

ClubLevelMove = Cells(1, 11).End(xlDown).Offset(1, 0).Row

End Function


I'd greatly appreciate your help guy's and gals.

 

[Norie]

Try this.

Dim NextAvailable As Long

    With Sheets("database")

        .Cells(Rows.Count, 11).End(xlUp).Row+1
 
        .Cells(NextAvailable, 11) = YearTXTBX.Text
        .Cells(NextAvailable, 12) = ChairmensTXTBX.Text
        .Cells(NextAvailable, 13) = PresidentsTXTBX.Text
        .Cells(NextAvailable, 14) = ExecutiveTXTBX.Text
        .Cells(NextAvailable, 15) = LeadersClubTXTBX.Text
    End With
 
End Sub

 

[Willmonbo]

thanks for your reply,

does it matter that I am calling the user form with a button on a different sheet?

 

[Norie]

That might have been why the original code didn't work.

The code I posted explicilty tells VBA to look at the worksheet 'Database' for the next row and to put the data on that worksheet.

I'm assuming that's where you want it.

Is the code I posted not working in some way?

 

[Willmonbo]

i haven't tried it yet, I just wanted to make sure that I'm doing it correctly, so do you want me to take out the function lines?

 

[Norie]

Sorry I made a mistake in the code.

Dim NextAvailable As Long

    With Sheets("database")

        NextAvailable = .Cells(Rows.Count, 11).End(xlUp).Row + 1
        .Cells(NextAvailable, 11) = YearTXTBX.Text
        .Cells(NextAvailable, 12) = ChairmensTXTBX.Text
        .Cells(NextAvailable, 13) = PresidentsTXTBX.Text
        .Cells(NextAvailable, 14) = ExecutiveTXTBX.Text
        .Cells(NextAvailable, 15) = LeadersClubTXTBX.Text
    End With
 
End Sub

As for the function, you could use it but you might want to change the code a bit.

Perhaps this:

ClubLevelMove=Sheets("database").Cells(Rows.Count, 11).End(xlUp).Row+1

Though I don't know if one line of code merits a separate function.

 

[Willmonbo]

Hi there

I am sorry that I couldn't try your code out till now, I did try it and it fails to write the data to the correct sheet.

let me just point it out again, I am calling the user form with a button on "sheet1" and the data should be going on the "database" sheet.

 

[Norie]

The code I posted specifically refers to the worksheet called 'database' so it should be posting data to that workshet.

There's is no way it would post the data to another worksheet unless the code has been changed.

If you are using the exact code I posted the only thing I can think of being a problem is the calculation of the next available row.

Even if that was a problem the data would still be posted to the 'database' worksheet, but perhaps not where you expected/wanted it to be posted.

 

[Willmonbo]

you're actually right, I forgot to modify one part of the code.