3 replies to this topic

[d0uga1]

im trying to code a spreadsheet in excel using the vba editor

i have a couple of userforms which holds data inputted by the user of the form what i need is some help with the code for transfering the data from the text boxes and comboboxes to my spreadsheet in the next empty colums i have some code in which transfers the data to an empty cell and a msgbox which ask user if they wish to enter anymore data if the user clicks yes the input boxes on the user form clear and when the user tries to input more data it copies it to the same cells. i need help on making this code so that when a user has put in the first set of data it automatically increases the the number of the next row.

here is the code i have for it so far please help me if you can thankyou


Private Sub Cmd1_Click()
Dim LastRow As Object

Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(67, 2).Value = TextBox1.Text
LastRow.Offset(67, 3).Value = TextBox2.Text
LastRow.Offset(67, 4).Value = ComboBox1.Text

MsgBox "Song Added To Database"

Response = MsgBox("Do you want to enter another Song To Database?", _
vbYesNo)

If Response = vbYes Then

TextBox1.Text = ""
TextBox2.Text = ""
ComboBox1.Text = ""
LastRow.Offset = LastRow.Offset + 1
TextBox1.SetFocus


Else
Unload Me
End If

End Sub

[nails]

if all else fails, try

del *.*

:evil:

[Smiley]

At first glance, your code appears to be treating the "LastRow.Offset" as a variable, which it isn't, and hence your code that sets the cell data always places them in the same cell (because you haven't used a variable in:

LastRow.Offset(67, 2).Value = TextBox1.Text
LastRow.Offset(67, 3).Value = TextBox2.Text
LastRow.Offset(67, 4).Value = ComboBox1.Text

You can search your database for an empty cell with:

x=0
While Not range("a65536").offset(x,0).value = ""
x = x +1
Wend

and then update the empty cell you find with:

LastRow.Offset(x, 2).Value = TextBox1.Text
LastRow.Offset(x, 3).Value = TextBox2.Text
LastRow.Offset(x, 4).Value = ComboBox1.Text

That's off the top of my head, so check the actual code, but the prinicipal works as I've used it myself in the past.

Smiley.

[d0uga1]

thanks smileyarcade i tried your code and unfortunatley it didnt work for me i keep getting an error but not to worry ill leave it for a few days and see if it somehow magically appears in my thoughts (cause it does sometimes you know lol)

but thanks for the effort its much appriciated.