I created a UserForm named UserForm2 with 4 Textboxes named Textbox1,
Textbox2, TextBox3, and Textbox4. I want to call UserForm2 (UserForm2.Show)
and have the following procedure execute.

-TextBox1 populate with random data from one cell in Sheet1 range B1:B200

-TextBox2, TextBox3, TextBox4 each populate with data from one different
random cell Sheet2 range B1:B200

I can insert this code below into TextBox1 of UserForm2 and it will pull
random data from one cell in Sheet1 range B1:B200 when I call the form

Private Sub UserForm_Initialize()
TextBox1.Text = Range("B" & Int(123 * Rnd) + 2).Value
End Sub

However if activate Sheet2 (Sheet2.Activate) and try to use the same code
for TextBox2, TextBox3, and TextBox4, I get no result; the textboxes remain
blank. Can you help me understand why my procedure is not working and how I
can get the result I am looking for with TextBox2, TextBox3, and TextBox4?

TextBox1.Text = Range("B" & Int(123 * Rnd) + 2).Value

I assume you are changing the TextBox name. You also need to Qualify your
range reference with the applicable sheet object, i.e.

TextBox2.Text = Sheets("Sheet2").Range("B" & Int(123*Rnd + 2).Value

Otherwise, it reverts to the active sheet.




"Rock" <(E-Mail Removed)> wrote in message
news:BE7121E9-A5C5-46BB-AD72-(E-Mail Removed)...
>I created a UserForm named UserForm2 with 4 Textboxes named Textbox1,
> Textbox2, TextBox3, and Textbox4. I want to call UserForm2
> (UserForm2.Show)
> and have the following procedure execute.
>
> -TextBox1 populate with random data from one cell in Sheet1 range B1:B200
>
> -TextBox2, TextBox3, TextBox4 each populate with data from one different
> random cell Sheet2 range B1:B200
>
> I can insert this code below into TextBox1 of UserForm2 and it will pull
> random data from one cell in Sheet1 range B1:B200 when I call the form
>
> Private Sub UserForm_Initialize()
> TextBox1.Text = Range("B" & Int(123 * Rnd) + 2).Value
> End Sub
>
> However if activate Sheet2 (Sheet2.Activate) and try to use the same code
> for TextBox2, TextBox3, and TextBox4, I get no result; the textboxes
> remain
> blank. Can you help me understand why my procedure is not working and how
> I
> can get the result I am looking for with TextBox2, TextBox3, and TextBox4?
>

Try specifying the sheet that contains that range:

Private Sub UserForm_Initialize()
TextBox1.Text = worksheets("Sheet1").Range("B" & Int(123 * Rnd) + 2).Value
TextBox2.Text = worksheets("Sheet2").Range("B" & Int(123 * Rnd) + 2).Value
TextBox3.Text = worksheets("Sheet2").Range("B" & Int(123 * Rnd) + 2).Value
TextBox4.Text = worksheets("Sheet2").Range("B" & Int(123 * Rnd) + 2).Value
End Sub

Rock wrote:
>
> I created a UserForm named UserForm2 with 4 Textboxes named Textbox1,
> Textbox2, TextBox3, and Textbox4. I want to call UserForm2 (UserForm2.Show)
> and have the following procedure execute.
>
> -TextBox1 populate with random data from one cell in Sheet1 range B1:B200
>
> -TextBox2, TextBox3, TextBox4 each populate with data from one different
> random cell Sheet2 range B1:B200
>
> I can insert this code below into TextBox1 of UserForm2 and it will pull
> random data from one cell in Sheet1 range B1:B200 when I call the form
>
> Private Sub UserForm_Initialize()
> TextBox1.Text = Range("B" & Int(123 * Rnd) + 2).Value
> End Sub
>
> However if activate Sheet2 (Sheet2.Activate) and try to use the same code
> for TextBox2, TextBox3, and TextBox4, I get no result; the textboxes remain
> blank. Can you help me understand why my procedure is not working and how I
> can get the result I am looking for with TextBox2, TextBox3, and TextBox4?

--

Dave Peterson

Got it. Thank you JLGWhiz!

"JLGWhiz" wrote:

> TextBox1.Text = Range("B" & Int(123 * Rnd) + 2).Value
>
> I assume you are changing the TextBox name. You also need to Qualify your
> range reference with the applicable sheet object, i.e.
>
> TextBox2.Text = Sheets("Sheet2").Range("B" & Int(123*Rnd + 2).Value
>
> Otherwise, it reverts to the active sheet.
>
>
>
>
> "Rock" <(E-Mail Removed)> wrote in message
> news:BE7121E9-A5C5-46BB-AD72-(E-Mail Removed)...
> >I created a UserForm named UserForm2 with 4 Textboxes named Textbox1,
> > Textbox2, TextBox3, and Textbox4. I want to call UserForm2
> > (UserForm2.Show)
> > and have the following procedure execute.
> >
> > -TextBox1 populate with random data from one cell in Sheet1 range B1:B200
> >
> > -TextBox2, TextBox3, TextBox4 each populate with data from one different
> > random cell Sheet2 range B1:B200
> >
> > I can insert this code below into TextBox1 of UserForm2 and it will pull
> > random data from one cell in Sheet1 range B1:B200 when I call the form
> >
> > Private Sub UserForm_Initialize()
> > TextBox1.Text = Range("B" & Int(123 * Rnd) + 2).Value
> > End Sub
> >
> > However if activate Sheet2 (Sheet2.Activate) and try to use the same code
> > for TextBox2, TextBox3, and TextBox4, I get no result; the textboxes
> > remain
> > blank. Can you help me understand why my procedure is not working and how
> > I
> > can get the result I am looking for with TextBox2, TextBox3, and TextBox4?
> >
>
>
> .
>

This is very helpful Dave. Thank you!

"Dave Peterson" wrote:

> Try specifying the sheet that contains that range:
>
> Private Sub UserForm_Initialize()
> TextBox1.Text = worksheets("Sheet1").Range("B" & Int(123 * Rnd) + 2).Value
> TextBox2.Text = worksheets("Sheet2").Range("B" & Int(123 * Rnd) + 2).Value
> TextBox3.Text = worksheets("Sheet2").Range("B" & Int(123 * Rnd) + 2).Value
> TextBox4.Text = worksheets("Sheet2").Range("B" & Int(123 * Rnd) + 2).Value
> End Sub
>
> Rock wrote:
> >
> > I created a UserForm named UserForm2 with 4 Textboxes named Textbox1,
> > Textbox2, TextBox3, and Textbox4. I want to call UserForm2 (UserForm2.Show)
> > and have the following procedure execute.
> >
> > -TextBox1 populate with random data from one cell in Sheet1 range B1:B200
> >
> > -TextBox2, TextBox3, TextBox4 each populate with data from one different
> > random cell Sheet2 range B1:B200
> >
> > I can insert this code below into TextBox1 of UserForm2 and it will pull
> > random data from one cell in Sheet1 range B1:B200 when I call the form
> >
> > Private Sub UserForm_Initialize()
> > TextBox1.Text = Range("B" & Int(123 * Rnd) + 2).Value
> > End Sub
> >
> > However if activate Sheet2 (Sheet2.Activate) and try to use the same code
> > for TextBox2, TextBox3, and TextBox4, I get no result; the textboxes remain
> > blank. Can you help me understand why my procedure is not working and how I
> > can get the result I am looking for with TextBox2, TextBox3, and TextBox4?
>
> --
>
> Dave Peterson
> .
>