Excel Data entry Foam

[billposter]

Hello all you excel buffs a bit of help if i can get it ??? i have created a data entry form to record matches (Football) i am interested in,( see screen shot) i have my worksheet and my data entry form where i input the data so i would like to do that but copy the data into the data entry form, also only show the form in full screen.
-----------------------------------------------------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()
Dim cNum As Integer
Dim X As Integer
Dim nextrow As Range

'change the number for the number of controls on the userform
cNum = 16
Set nextrow = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next

End Sub
------------------------------------------------------------------------------------------------------------------------------------------
The formula above inputs the data in the worksheet,when i use the next line of code
I get this
-------------------------------------------------------------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()
Dim cNum As Integer
Dim X As Integer
Dim nextrow As Range

'change the number for the number of controls on the userform
cNum = 16
Set nextrow = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next

lstDisplay.ColumnCount = 16
lstDisplay.RowSource = "D1:R2000 "

End Sub
--------------------------------------------------------------------------------------------------------------------------------------------
The text in bold is the error High lighted in yellow (Object Required) i have changed things around with no luck,I know it could be something simple but cannot work it out!!!!

Hope that all makes sense ......

[eightbo]

lstDisplay.ColumnCount = 16
lstDisplay.RowSource = "D1:R2000 "

The text in bold is the error High lighted in yellow (Object Required)

Hi there Bill. It looks like you are trying to assign a value of 16 to a variable which you have not declared (lstDisplay).
A good way to think about this is if I asked you to give £5 to Joe Robertson, you'd be thinking "who now??"

It also looks like .ColumnCount and .RowSource are for MS Access VBA so we need to swap out those too.

I'm assuming you want to store the number 16 and cells D1:R2000 so you can do things with them,
I have created a macro for you which does this that you can play with:

Code: Select all

Sub billposter()
    'Declare variables for use
    Dim numDisplay As Integer
    Dim rngDisplay As String
    
    'Store values in the variables
    numDisplay = 16
    rngDisplay = "D1:R2000"
    
    'Check we have successfully stored something in the variable 'numDisplay'
    MsgBox "Variable 'numDisplay' is: " & numDisplay, vbInformation
    
    'Check we can successfully do things with the variable 'rngDisplay'
    Range(rngDisplay).Select
End Sub

[billposter]

lstDisplay.ColumnCount = 16
lstDisplay.RowSource = "D1:R2000 "

The text in bold is the error High lighted in yellow (Object Required)

Hi there Bill. It looks like you are trying to assign a value of 16 to a variable which you have not declared (lstDisplay).
A good way to think about this is if I asked you to give £5 to Joe Robertson, you'd be thinking "who now??"

It also looks like .ColumnCount and .RowSource are for MS Access VBA so we need to swap out those too.

I'm assuming you want to store the number 16 and cells D1:R2000 so you can do things with them,
I have created a macro for you which does this that you can play with:

Code: Select all

Sub billposter()
    'Declare variables for use
    Dim numDisplay As Integer
    Dim rngDisplay As String
    
    'Store values in the variables
    numDisplay = 16
    rngDisplay = "D1:R2000"
    
    'Check we have successfully stored something in the variable 'numDisplay'
    MsgBox "Variable 'numDisplay' is: " & numDisplay, vbInformation
    
    'Check we can successfully do things with the variable 'rngDisplay'
    Range(rngDisplay).Select
End Sub

Thanks for the quick reply will this code work with my (add new bet command button??)
and i am assuming i start a new formula with the code or do i just add it to the bottom of my original code???
sorry for the daft questions but very new to vba coding

[eightbo]

That's right you want to add it to your code. Replace what you have with this:

Code: Select all

Private Sub CommandButton1_Click()

    'Declare variables
    Dim cNum As Integer
    Dim X As Integer
    Dim nextrow As Range
    Dim numDisplay As Integer
    Dim rngDisplay As String

    'change the number for the number of controls on the userform
    cNum = 16
    Set nextrow = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
    For X = 1 To cNum
    nextrow = Me.Controls("Reg" & X).Value
    Set nextrow = nextrow.Offset(0, 1)
    Next

    'Store values in the variables
    numDisplay = 16
    rngDisplay = "D1:R2000"

    'Check we have successfully stored something in the variable 'numDisplay'
    MsgBox "Variable 'numDisplay' is: " & numDisplay, vbInformation
    
    'Check we can successfully do things with the variable 'rngDisplay'
    Range(rngDisplay).Select
    
End Sub

You can delete the last 4 lines after you've had a play with them and then carry on.

[nigelk]

You could be getting the error because it can't find an object (label,textbox, combobox etc ) named "lstdisplay" on your data entry form.

I inserted a userform, and kept the default name, then a listbox and kept that default name.

The code below works fine.

Code: Select all

UserForm1.ListBox1.ColumnCount = 16
UserForm1.ListBox1.RowSource = "D1:R2000 "

Find out the names of your form and listbox and change to suit.

[billposter]

Thanks for the help Gentelmen but it is back to the drawing board none of them worked thanks again for you help