Can I make a variable on a Userform be seen by the Worksheet_Activate()
method of a tab? How?

David,
As the Userform code is a class module, you can add Public variables or
Property Let/Get/Set that can be accessed from outside it. If the Userform
is not loaded, it will be:

'<Userform code>
Public SomeVar As String

Private Sub UserForm_Initialize()
SomeVar = "New Value"
End Sub
'</Userform code>

'<WS code>
Private Sub Worksheet_Activate()
MsgBox UserForm1.SomeVar
End Sub
'</WS code>

NickHK

"David Portwood" <(E-Mail Removed)> wrote in message
news:4611bf49$0$9882$(E-Mail Removed)...
> Can I make a variable on a Userform be seen by the Worksheet_Activate()
> method of a tab? How?
>
>

David,

I set up a form - userform1 with one textbox, textbox1.
The code behind the form is:

Public x
Private Sub TextBox1_Change()
x = TextBox1.Value
End Sub

I set the form to be non-modal.

I have 3 sheets with the following code in them:

Sheet1:
Private Sub Worksheet_Activate()
MsgBox (UserForm1.TextBox1.Value)
End Sub

Sheet2:
Private Sub Worksheet_Activate()
MsgBox (UserForm1.x)
End Sub

Sheet3:
Private Sub Worksheet_Activate()
UserForm1.Show
End Sub

I think this gets at what you want.




"David Portwood" wrote:

> Can I make a variable on a Userform be seen by the Worksheet_Activate()
> method of a tab? How?
>
>
>

I knew I could use dot notation to reference a GUI object such as a text box
on a form, but I didn't know I could reference a variable that way. That
would certainly solve my problem. I'll try it at work in the morning.

"NickHK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> David,
> As the Userform code is a class module, you can add Public variables or
> Property Let/Get/Set that can be accessed from outside it. If the Userform
> is not loaded, it will be:
>
> '<Userform code>
> Public SomeVar As String
>
> Private Sub UserForm_Initialize()
> SomeVar = "New Value"
> End Sub
> '</Userform code>
>
> '<WS code>
> Private Sub Worksheet_Activate()
> MsgBox UserForm1.SomeVar
> End Sub
> '</WS code>
>
> NickHK
>
> "David Portwood" <(E-Mail Removed)> wrote in message
> news:4611bf49$0$9882$(E-Mail Removed)...
>> Can I make a variable on a Userform be seen by the Worksheet_Activate()
>> method of a tab? How?
>>
>>
>
>

Thanks for responding, Art.

I'm looking to set the value of a range object which points to the values of
the current row of a list object. I have many list objects of the same
structure, one on each of about 20 worksheets. When the user switches
between worksheets, I want to reset that range to the first row of the list
object on whichever worksheet he activates. My Userform uses this range
object to allow the user to modify the values in that row.

"Art" <(E-Mail Removed)> wrote in message
news3843B23-B4AA-443D-A31E-(E-Mail Removed)...
> David,
>
> I set up a form - userform1 with one textbox, textbox1.
> The code behind the form is:
>
> Public x
> Private Sub TextBox1_Change()
> x = TextBox1.Value
> End Sub
>
> I set the form to be non-modal.
>
> I have 3 sheets with the following code in them:
>
> Sheet1:
> Private Sub Worksheet_Activate()
> MsgBox (UserForm1.TextBox1.Value)
> End Sub
>
> Sheet2:
> Private Sub Worksheet_Activate()
> MsgBox (UserForm1.x)
> End Sub
>
> Sheet3:
> Private Sub Worksheet_Activate()
> UserForm1.Show
> End Sub
>
> I think this gets at what you want.
>
>
>
>
> "David Portwood" wrote:
>
>> Can I make a variable on a Userform be seen by the Worksheet_Activate()
>> method of a tab? How?
>>
>>
>>

David,

Well, I didn't know what ListObjects were -- so this was an interesting
experience, even if it turns out I'm not able to help you.

This is what I did. I created 4 sheets each with a similar ListObject --
but just for fun, in different places. I don't know how you can see what the
name of the ListObject is, so I put in a For Each loop, counting on there
being only one.

I have a form, UserForm1. I gave it the property Show Modal = False. The
form has one Text Box, TextBox1.

On each of the 4 sheets I have this routine:

Private Sub Worksheet_Activate()
Dim lo As ListObject
For Each lo In Me.ListObjects
Set mRange = lo.Range.Rows(2)
Next
UserForm1.TextBox1 = mRange.Cells(1, 3)
End Sub

If I activate the form (I used a simple routine in a module) and tab from
sheet to sheet, the value in TextBox1 changes based on mRange.

I hope this is at least close to what you were looking for. Thanks for the
learning experience.

"David Portwood" wrote:

> Thanks for responding, Art.
>
> I'm looking to set the value of a range object which points to the values of
> the current row of a list object. I have many list objects of the same
> structure, one on each of about 20 worksheets. When the user switches
> between worksheets, I want to reset that range to the first row of the list
> object on whichever worksheet he activates. My Userform uses this range
> object to allow the user to modify the values in that row.
>
> "Art" <(E-Mail Removed)> wrote in message
> news3843B23-B4AA-443D-A31E-(E-Mail Removed)...
> > David,
> >
> > I set up a form - userform1 with one textbox, textbox1.
> > The code behind the form is:
> >
> > Public x
> > Private Sub TextBox1_Change()
> > x = TextBox1.Value
> > End Sub
> >
> > I set the form to be non-modal.
> >
> > I have 3 sheets with the following code in them:
> >
> > Sheet1:
> > Private Sub Worksheet_Activate()
> > MsgBox (UserForm1.TextBox1.Value)
> > End Sub
> >
> > Sheet2:
> > Private Sub Worksheet_Activate()
> > MsgBox (UserForm1.x)
> > End Sub
> >
> > Sheet3:
> > Private Sub Worksheet_Activate()
> > UserForm1.Show
> > End Sub
> >
> > I think this gets at what you want.
> >
> >
> >
> >
> > "David Portwood" wrote:
> >
> >> Can I make a variable on a Userform be seen by the Worksheet_Activate()
> >> method of a tab? How?
> >>
> >>
> >>
>
>
>

Worked like a charm. I am new to VBA and although it is complex, I am
impressed by its flexibility. Seems like everything is possible. Thanks
again.

"David Portwood" <(E-Mail Removed)> wrote in message
news:4611dd59$0$18897$(E-Mail Removed)...
>I knew I could use dot notation to reference a GUI object such as a text
>box on a form, but I didn't know I could reference a variable that way.
>That would certainly solve my problem. I'll try it at work in the morning.
>
> "NickHK" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> David,
>> As the Userform code is a class module, you can add Public variables or
>> Property Let/Get/Set that can be accessed from outside it. If the
>> Userform
>> is not loaded, it will be:
>>
>> '<Userform code>
>> Public SomeVar As String
>>
>> Private Sub UserForm_Initialize()
>> SomeVar = "New Value"
>> End Sub
>> '</Userform code>
>>
>> '<WS code>
>> Private Sub Worksheet_Activate()
>> MsgBox UserForm1.SomeVar
>> End Sub
>> '</WS code>
>>
>> NickHK
>>
>> "David Portwood" <(E-Mail Removed)> wrote in message
>> news:4611bf49$0$9882$(E-Mail Removed)...
>>> Can I make a variable on a Userform be seen by the Worksheet_Activate()
>>> method of a tab? How?
>>>
>>>
>>
>>
>
>

I'm sure there are many ways to skin this cat. Your way looks more direct.

I am also climbing the learning curve. It's great to have this group
available to go to for help.

"Art" <(E-Mail Removed)> wrote in message
news:7AE616DD-B1E5-4CB9-8378-(E-Mail Removed)...
> David,
>
> Well, I didn't know what ListObjects were -- so this was an interesting
> experience, even if it turns out I'm not able to help you.
>
> This is what I did. I created 4 sheets each with a similar ListObject --
> but just for fun, in different places. I don't know how you can see what
> the
> name of the ListObject is, so I put in a For Each loop, counting on there
> being only one.
>
> I have a form, UserForm1. I gave it the property Show Modal = False. The
> form has one Text Box, TextBox1.
>
> On each of the 4 sheets I have this routine:
>
> Private Sub Worksheet_Activate()
> Dim lo As ListObject
> For Each lo In Me.ListObjects
> Set mRange = lo.Range.Rows(2)
> Next
> UserForm1.TextBox1 = mRange.Cells(1, 3)
> End Sub
>
> If I activate the form (I used a simple routine in a module) and tab from
> sheet to sheet, the value in TextBox1 changes based on mRange.
>
> I hope this is at least close to what you were looking for. Thanks for
> the
> learning experience.
>
> "David Portwood" wrote:
>
>> Thanks for responding, Art.
>>
>> I'm looking to set the value of a range object which points to the values
>> of
>> the current row of a list object. I have many list objects of the same
>> structure, one on each of about 20 worksheets. When the user switches
>> between worksheets, I want to reset that range to the first row of the
>> list
>> object on whichever worksheet he activates. My Userform uses this range
>> object to allow the user to modify the values in that row.
>>
>> "Art" <(E-Mail Removed)> wrote in message
>> news3843B23-B4AA-443D-A31E-(E-Mail Removed)...
>> > David,
>> >
>> > I set up a form - userform1 with one textbox, textbox1.
>> > The code behind the form is:
>> >
>> > Public x
>> > Private Sub TextBox1_Change()
>> > x = TextBox1.Value
>> > End Sub
>> >
>> > I set the form to be non-modal.
>> >
>> > I have 3 sheets with the following code in them:
>> >
>> > Sheet1:
>> > Private Sub Worksheet_Activate()
>> > MsgBox (UserForm1.TextBox1.Value)
>> > End Sub
>> >
>> > Sheet2:
>> > Private Sub Worksheet_Activate()
>> > MsgBox (UserForm1.x)
>> > End Sub
>> >
>> > Sheet3:
>> > Private Sub Worksheet_Activate()
>> > UserForm1.Show
>> > End Sub
>> >
>> > I think this gets at what you want.
>> >
>> >
>> >
>> >
>> > "David Portwood" wrote:
>> >
>> >> Can I make a variable on a Userform be seen by the
>> >> Worksheet_Activate()
>> >> method of a tab? How?
>> >>
>> >>
>> >>
>>
>>
>>