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 ......
Excel Data entry Foam
-
- Posts: 30
- Joined: Wed Feb 01, 2017 1:45 pm
You do not have the required permissions to view the files attached to this post.
Hi there Bill. It looks like you are trying to assign a value of 16 to a variable which you have not declared (lstDisplay).billposter wrote: ↑Mon Mar 25, 2019 2:37 amlstDisplay.ColumnCount = 16
lstDisplay.RowSource = "D1:R2000 "
The text in bold is the error High lighted in yellow (Object Required)
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
-
- Posts: 30
- Joined: Wed Feb 01, 2017 1:45 pm
Thanks for the quick reply will this code work with my (add new bet command button??)eightbo wrote: ↑Mon Mar 25, 2019 3:31 amHi there Bill. It looks like you are trying to assign a value of 16 to a variable which you have not declared (lstDisplay).billposter wrote: ↑Mon Mar 25, 2019 2:37 amlstDisplay.ColumnCount = 16
lstDisplay.RowSource = "D1:R2000 "
The text in bold is the error High lighted in yellow (Object Required)
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
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
That's right you want to add it to your code. Replace what you have with this:
You can delete the last 4 lines after you've had a play with them and then carry on.
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 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.
Find out the names of your form and listbox and change to suit.
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 "
-
- Posts: 30
- Joined: Wed Feb 01, 2017 1:45 pm
Thanks for the help Gentelmen but it is back to the drawing board none of them worked thanks again for you help