UserForm code to enter control values from multiple pages

svalente34 · Apr 19th 2016

Hi All,

I'm trying to build a UserForm that has 878 controls on 8 pages of a multipage control. It's my first time creating one and I'm trying to use a code I found off one tutorial but I keep getting an error message and I'm hoping someone here might have some insight.

Each control is labeled consecutively r1-r878, I've checked that all controls are in order. And in the code area for the SUBMIT button i have the following code:

Code

Private Sub CommandSubmit_Click()


Dim X As Integer
Dim nextrow As Range




Set nextrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
For X = 1 To 878
nextrow = Me.Controls("r" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next




End Sub

Display More

If I test the form, the information is entered into the spreadsheet but a message box appears with the following:

Run-time error '-2147024809 (80070057)':
Could not find the specified object.

The debug function points to the line: nextrow=Me.Controls("r" & X).Value

Is there any way to fix the bug?

Thank you!

**pike** · Apr 20th 2016

Re: UserForm code to enter control values from multiple pages

Hello svalente34
Welcome to the forum
The page
http://www.ozgrid.com/VBA/control-loop.htm

Code

Dim pPage As Page, cCont As Control,lngRow as long






    For Each pPage In Me.MultiPage1.Pages


        For Each cCont In pPage.Controls


            If TypeName(cCont) = "TextBox" Then


               'DO STUFF HERE
            lngRow =lngRow+1
            cCont.value=   Sheet1.Cells(lngRow, 1).
            End If


        Next cCont


    Next pPage

Display More

UserForm code to enter control values from multiple pages

Participate now!

Share