VB Code help

zpierucci

New Member
Joined
Sep 5, 2019
Messages
42
I am trying to get my info from my text boxes in my user form into my worksheet. I have done this multiple time before and had easy success but now I'm getting problems. I either gut a runtime error or there is no data transferred. This below transfers no data to the worksheet
Code:
Private Sub CommandButton1_Click()
Dim cNum As Integer
Dim X As Integer
Dim nextrow As Range


cNum = 4
Set nextrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next


Reg1.Text = ""
Reg2.Text = ""
Reg3.Text = ""
Reg4.Text = ""
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Welcome to the Board!

I am not so sure that you can cycle through all the "Reg" controls like this:
Code:
nextrow = Me.Controls([COLOR=#ff0000]"Reg" & X[/COLOR]).Value
What happens if you try hard-coding it, like this?
Code:
nextrow = Me.Controls([COLOR=#ff0000]"Reg1"[/COLOR]).Value

If that works, that confirms that you cannot build the control names dynamically like you have tried.
Maybe consider creating an array that holds all four control names, and iterating through that?
 
Upvote 0
Hi & welcome to MrExcel.
There is nothing obviously wrong with the code.
Do you have 4 textboxes named Reg1, Reg2 etc?
What line of code gives the error & what is the error number & message?
 
Upvote 0
Hi Fluff, Yes all textboxes are named Reg1 - Reg4. In this case I am getting now error at all. There is just no text being transferred from the userform1 to the excel sheet.
 
Upvote 0
If you add the line in blue, what does the msgbox say
Code:
Set nextrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
[COLOR=#0000ff]MsgBox nextrow.Address[/COLOR]
For X = 1 To cNum
 
Upvote 0
Is that the first blank row in col A?
Because your data should go into A15:D15 on sheet2
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top