How to reference a range from a UserForm

[Patrick C. Simonds]

I would like to put this code in a module outside of UserForm1 but the
module stops when it gets to rng1(1, 6).

The UserForm is displayed when the code is run and there is a line:

Dim rng1 As Range

in the General Declaration area of the UserForm, and I have the following:

Set rng1 = Cells(ActiveCell.Row, 1)

which run prior to calling the routine below.



Sub TextBoxData()

'No Show 1

UserForm1.TextBox2202.Value = rng1(1, 6)
'Date of No Show
UserForm1.TextBox2202.Value = Format$(UserForm1.TextBox2202.Value,
"ddd dd mmm yy")
UserForm1.TextBox2203.Value = rng1(1, 7)

End Sub

 

[Joel]

The code doesn't know you are refereing to a worksheet or you have a chart
sheet that is active. Try this

with activesheet
Set rng1 = .Cells(.ActiveCell.Row, 1)
end with

 

[Patrick C. Simonds]

The range rng1 was set by Userform1 and and I need to refer to that value.

 

[Joel]

when rng1 was set is contains a reference to a worksheet. It must be the
worksheet whre the data is going to be placed. Even if you first set a
worksheet active and then set rng1, rng1 has a parent which is the current
active worksheet at the time the variable was set.

Activecell is the current activecell on the current active worksheet. It
may not be the same as athe parent of rng1.

When you are working on a userform the focus is on the userform and not on
any worksheet. the only time you can guarentee which worksheet is the active
worksheet is when you either have a UDF function or when you have a worksheet
event and the macro is on one of the VBA sheets (not moule, or thisworkbook).

Just posting a piece of your code without seeing the rest of the macro I
can't tell where the focus is located which is probably causing your

 

[Patrick C. Simonds]

I recognize that I did not make my situation very clear and that with only a
snippet of code ( in total there are over 5000 lines) it is nearly
impossible to get a sense of what is happening.

So I will try to explain what I need again and hopefully being more clear
(no promises there).

In the General Declaration for UserForm1 I declarer the following Ranges:

Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range
Dim rng7 As Range
Dim rng8 As Range
Dim rng9 As Range

During the course UserForm Initialization all 10 of the rng's are set.

'No Show #1 Data
Range("A3").Select
Call NextRow
Set rng1 = Cells(ActiveCell.Row, 1)
If rng1(1, 1) = rng(1, 1) Then
Frame7.Visible = False
End If

'No Show #2 Data
Range("A3").Select
Call NextRow
Set rng2 = Cells(ActiveCell.Row, 1)
If rng2(1, 1) = rng(1, 1) Then
Frame8.Visible = False
End If

This is repeated until all ranges are set.

While the UserForm is displayed (and it is triggered by clicking on the
active sheet) I want to be able to call a module which uses those ranges
during it's execution.

I hope that is more clear. If not I appreciate the time you have put into
this and I will try to rethink what I am trying to do.

 

[Joel]

The code you have posted should work by itself without the call to NextRow as
long as a worksheet is active and activecell.row is a positive number > 0 and
less then 65536. I can't tell from your code what NextRow is doing with the
active cell so I don't know the cause of the failure.

I would also change the following line to prevent any errors

from
rng1(1, 1) = rng(1, 1)

to

rng1(1, 1).value = rng(1, 1).value