Passing a variable through a UserForm 
Author Message
 Passing a variable through a UserForm

[Originally posted in the wrong group worksheet.functions.]

Is there a way to pass a variable through a UserForm?

For example, I have a UserForm with a command button and a textbox. When I
double-click on a worksheet, show the userform.When I click on the command
button in the UserForm, I want to put the value from the textbox in the
double-clicked cell on the worksheet.

Here is the *pseudo*-code:

'In the worksheet
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
  Cancel = True
  UserForm1.Show (Cell: = Target(1,1))
End Sub

'In the UserForm
Private Sub CommandButton1_Click(ByVal Cell as Range)
    Cell.Value = TextBox1.Value
End Sub

Thanks.
Tim



Mon, 13 Dec 2004 13:50:40 GMT  
 Passing a variable through a UserForm
Hi Tim

'MUST be at the very top of any Standard Module.
Public rCell as Range

'In the worksheet
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
set rCell =Target
  Cancel = True
  UserForm1.Show
End Sub

'In the UserForm
Private Sub CommandButton1_Click(ByVal Cell as Range)
    rCell  = TextBox1.Value
End Sub

Hope this helps

--
Kind Regards
Dave Hawley
www.MicrosoftExcelTraining.com
www.OzGrid.com
FREE EXCEL NEWSLETTER
http://www.ozgrid.com/News/2home.htm
Get the OzGrid Add-in
http://www.ozgrid.com/Services/AddinExamples.htm
If it's Excel, then it's us!


| [Originally posted in the wrong group worksheet.functions.]
|
| Is there a way to pass a variable through a UserForm?
|
| For example, I have a UserForm with a command button and a textbox. When I
| double-click on a worksheet, show the userform.When I click on the command
| button in the UserForm, I want to put the value from the textbox in the
| double-clicked cell on the worksheet.
|
| Here is the *pseudo*-code:
|
| 'In the worksheet
| Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
| Boolean)
|   Cancel = True
|   UserForm1.Show (Cell: = Target(1,1))
| End Sub
|
| 'In the UserForm
| Private Sub CommandButton1_Click(ByVal Cell as Range)
|     Cell.Value = TextBox1.Value
| End Sub
|
| Thanks.
| Tim
|
|
|



Mon, 13 Dec 2004 14:14:36 GMT  
 Passing a variable through a UserForm

'In the UserForm
Dim mMyRange as Range

Private Sub CommandButton1_Click()
    mMyRange.value = TextBox1.Value
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
      Cancel = True
      UserForm1.Load
      set UserFrom1.mMyRange = Target(1,1)
      userForm1.Show
End Sub

HTH,

--
Earl K. Takasaki

Please reply to the group.


[Originally posted in the wrong group worksheet.functions.]

Is there a way to pass a variable through a UserForm?

For example, I have a UserForm with a command button and a textbox. When I
double-click on a worksheet, show the userform.When I click on the command
button in the UserForm, I want to put the value from the textbox in the
double-clicked cell on the worksheet.

Here is the *pseudo*-code:

'In the worksheet
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
  Cancel = True
  UserForm1.Show (Cell: = Target(1,1))
End Sub

'In the UserForm
Private Sub CommandButton1_Click(ByVal Cell as Range)
    Cell.Value = TextBox1.Value
End Sub

Thanks.
Tim



Mon, 13 Dec 2004 14:06:57 GMT  
 Passing a variable through a UserForm
Hello Tim
Here's one way to achieve this:
- Declare a public variable in a standard module:
Public MyCell
- In you Worksheet_BeforeDoubleClick add this:
Cancel = True
MyCell = Target.Address(False, False)
Load UserForm1
UserForm1.Show
- In your CommandButton put this:
Private Sub CommandButton1_Click()
Me.Hide
ActiveSheet.Range(MyCell).Value = Me.TextBox1.Value
Unload Me
End Sub
Works for me (Excel 2K)

HTH
Regards

Pascal (France)



Quote:
> [Originally posted in the wrong group worksheet.functions.]

> Is there a way to pass a variable through a UserForm?

> For example, I have a UserForm with a command button and a textbox. When I
> double-click on a worksheet, show the userform.When I click on the command
> button in the UserForm, I want to put the value from the textbox in the
> double-clicked cell on the worksheet.

> Here is the *pseudo*-code:

> 'In the worksheet
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> Boolean)
>   Cancel = True
>   UserForm1.Show (Cell: = Target(1,1))
> End Sub

> 'In the UserForm
> Private Sub CommandButton1_Click(ByVal Cell as Range)
>     Cell.Value = TextBox1.Value
> End Sub

> Thanks.
> Tim



Mon, 13 Dec 2004 14:13:36 GMT  
 Passing a variable through a UserForm
Nice try but if the form is modeless then all you have to do is select
another worksheet before clicking the button, and your code would place the
textbox value in the cell address, but different worksheet (not the
Target.Parent) of the double-clicked cell.


Quote:
> Hello Tim
> Here's one way to achieve this:
> - Declare a public variable in a standard module:
> Public MyCell
> - In you Worksheet_BeforeDoubleClick add this:
> Cancel = True
> MyCell = Target.Address(False, False)
> Load UserForm1
> UserForm1.Show
> - In your CommandButton put this:
> Private Sub CommandButton1_Click()
> Me.Hide
> ActiveSheet.Range(MyCell).Value = Me.TextBox1.Value
> Unload Me
> End Sub
> Works for me (Excel 2K)

> HTH
> Regards

> Pascal (France)



> > [Originally posted in the wrong group worksheet.functions.]

> > Is there a way to pass a variable through a UserForm?

> > For example, I have a UserForm with a command button and a textbox. When
I
> > double-click on a worksheet, show the userform.When I click on the
command
> > button in the UserForm, I want to put the value from the textbox in the
> > double-clicked cell on the worksheet.

> > Here is the *pseudo*-code:

> > 'In the worksheet
> > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> > Boolean)
> >   Cancel = True
> >   UserForm1.Show (Cell: = Target(1,1))
> > End Sub

> > 'In the UserForm
> > Private Sub CommandButton1_Click(ByVal Cell as Range)
> >     Cell.Value = TextBox1.Value
> > End Sub

> > Thanks.
> > Tim



Mon, 13 Dec 2004 14:35:25 GMT  
 Passing a variable through a UserForm
Hi Dave:

I want to pass the variable (ByVal), not by using a public variable and then
reading from it later. Sorry for not being clearer.

Tim


Quote:
> Hi Tim

> 'MUST be at the very top of any Standard Module.
> Public rCell as Range

> 'In the worksheet
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> Boolean)
> set rCell =Target
>   Cancel = True
>   UserForm1.Show
> End Sub

> 'In the UserForm
> Private Sub CommandButton1_Click(ByVal Cell as Range)
>     rCell  = TextBox1.Value
> End Sub

> Hope this helps

> --
> Kind Regards
> Dave Hawley
> www.MicrosoftExcelTraining.com
> www.OzGrid.com
> FREE EXCEL NEWSLETTER
> http://www.ozgrid.com/News/2home.htm
> Get the OzGrid Add-in
> http://www.ozgrid.com/Services/AddinExamples.htm
> If it's Excel, then it's us!



> | [Originally posted in the wrong group worksheet.functions.]
> |
> | Is there a way to pass a variable through a UserForm?
> |
> | For example, I have a UserForm with a command button and a textbox. When
I
> | double-click on a worksheet, show the userform.When I click on the
command
> | button in the UserForm, I want to put the value from the textbox in the
> | double-clicked cell on the worksheet.
> |
> | Here is the *pseudo*-code:
> |
> | 'In the worksheet
> | Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> | Boolean)
> |   Cancel = True
> |   UserForm1.Show (Cell: = Target(1,1))
> | End Sub
> |
> | 'In the UserForm
> | Private Sub CommandButton1_Click(ByVal Cell as Range)
> |     Cell.Value = TextBox1.Value
> | End Sub
> |
> | Thanks.
> | Tim
> |
> |
> |



Mon, 13 Dec 2004 14:48:39 GMT  
 Passing a variable through a UserForm

Quote:
> 'In the UserForm
> Private Sub CommandButton1_Click(ByVal Cell as Range)
>     rCell  = TextBox1.Value
> End Sub

    A command button click event doesn't have any arguments that I'm aware
of.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *



Mon, 13 Dec 2004 14:51:39 GMT  
 Passing a variable through a UserForm
Hi Tim,

    I'd suggest creating a Caller property for your UserForm that would
store the range object you wanted to modify. Load the UserForm and set this
property to the range on the worksheet that was double-clicked before
displaying the UserForm. Then you'll always have a reference to the correct
cell. Something like this:

In the UserForm code module:
-------------------------------------------------------------
Private mrngCaller As Range

Public Property Set Caller(ByRef rngNewValue As Range)
    Set mrngCaller = rngNewValue
End Property

Private Sub CommandButton1_Click()
    mrngCaller.Value = TextBox1.Text
End Sub

In the worksheet code module:
------------------------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
    Load UserForm1
    Set UserForm1.Caller = Target
    UserForm1.Show
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


Quote:
> Nice try but if the form is modeless then all you have to do is select
> another worksheet before clicking the button, and your code would place
the
> textbox value in the cell address, but different worksheet (not the
> Target.Parent) of the double-clicked cell.



> > Hello Tim
> > Here's one way to achieve this:
> > - Declare a public variable in a standard module:
> > Public MyCell
> > - In you Worksheet_BeforeDoubleClick add this:
> > Cancel = True
> > MyCell = Target.Address(False, False)
> > Load UserForm1
> > UserForm1.Show
> > - In your CommandButton put this:
> > Private Sub CommandButton1_Click()
> > Me.Hide
> > ActiveSheet.Range(MyCell).Value = Me.TextBox1.Value
> > Unload Me
> > End Sub
> > Works for me (Excel 2K)

> > HTH
> > Regards

> > Pascal (France)



> > > [Originally posted in the wrong group worksheet.functions.]

> > > Is there a way to pass a variable through a UserForm?

> > > For example, I have a UserForm with a command button and a textbox.
When
> I
> > > double-click on a worksheet, show the userform.When I click on the
> command
> > > button in the UserForm, I want to put the value from the textbox in
the
> > > double-clicked cell on the worksheet.

> > > Here is the *pseudo*-code:

> > > 'In the worksheet
> > > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
> > > Boolean)
> > >   Cancel = True
> > >   UserForm1.Show (Cell: = Target(1,1))
> > > End Sub

> > > 'In the UserForm
> > > Private Sub CommandButton1_Click(ByVal Cell as Range)
> > >     Cell.Value = TextBox1.Value
> > > End Sub

> > > Thanks.
> > > Tim



Mon, 13 Dec 2004 14:58:29 GMT  
 Passing a variable through a UserForm
Hi Tim

Not sure I understand. Why not use a Public variable?

--
Kind Regards
Dave Hawley
www.MicrosoftExcelTraining.com
www.OzGrid.com
FREE EXCEL NEWSLETTER
http://www.ozgrid.com/News/2home.htm
Get the OzGrid Add-in
http://www.ozgrid.com/Services/AddinExamples.htm
If it's Excel, then it's us!


| Hi Dave:
|
| I want to pass the variable (ByVal), not by using a public variable and
then
| reading from it later. Sorry for not being clearer.
|
| Tim
|


| > Hi Tim
| >
| > 'MUST be at the very top of any Standard Module.
| > Public rCell as Range
| >
| > 'In the worksheet
| > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
| > Boolean)
| > set rCell =Target
| >   Cancel = True
| >   UserForm1.Show
| > End Sub
| >
| > 'In the UserForm
| > Private Sub CommandButton1_Click(ByVal Cell as Range)
| >     rCell  = TextBox1.Value
| > End Sub
| >
| >
| > Hope this helps
| >
| > --
| > Kind Regards
| > Dave Hawley
| > www.MicrosoftExcelTraining.com
| > www.OzGrid.com
| > FREE EXCEL NEWSLETTER
| > http://www.ozgrid.com/News/2home.htm
| > Get the OzGrid Add-in
| > http://www.ozgrid.com/Services/AddinExamples.htm
| > If it's Excel, then it's us!
| >
| >
| >


| > | [Originally posted in the wrong group worksheet.functions.]
| > |
| > | Is there a way to pass a variable through a UserForm?
| > |
| > | For example, I have a UserForm with a command button and a textbox.
When
| I
| > | double-click on a worksheet, show the userform.When I click on the
| command
| > | button in the UserForm, I want to put the value from the textbox in
the
| > | double-clicked cell on the worksheet.
| > |
| > | Here is the *pseudo*-code:
| > |
| > | 'In the worksheet
| > | Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
| > | Boolean)
| > |   Cancel = True
| > |   UserForm1.Show (Cell: = Target(1,1))
| > | End Sub
| > |
| > | 'In the UserForm
| > | Private Sub CommandButton1_Click(ByVal Cell as Range)
| > |     Cell.Value = TextBox1.Value
| > | End Sub
| > |
| > | Thanks.
| > | Tim
| > |
| > |
| > |
| >
| >
|
|



Mon, 13 Dec 2004 15:15:21 GMT  
 Passing a variable through a UserForm

    Just noticed I forgot the Cancel = True in the
Worksheet_BeforeDoubleClick event procedure, make sure to add that.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


Quote:
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> Boolean)
>     Load UserForm1
>     Set UserForm1.Caller = Target
>     UserForm1.Show
> End Sub



Mon, 13 Dec 2004 15:36:58 GMT  
 Passing a variable through a UserForm
Mostly a personal preference. When I am passing a value to a procedure I'd
like to try to be more direct about it.

For example, I'd rather use this approach:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub A()
    Call B(Num:=5)
End Sub

Sub B(ByVal Num as Integer)
    Msgbox "A passed me " & Num
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Than this:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Num as Integer

Sub A()
    Num = 5
    Call B
End Sub

Sub B()
    Msgbox "Num has the value of " & Num
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
I find the first approach easier to follow. The second requires some
assumptions and can be harder to track down especially if A and B exist in
different modules. Not likely, but Num could be changed somewhere else
before B displayed it.


Quote:
> Hi Tim

> Not sure I understand. Why not use a Public variable?

> --
> Kind Regards
> Dave Hawley
> www.MicrosoftExcelTraining.com
> www.OzGrid.com
> FREE EXCEL NEWSLETTER
> http://www.ozgrid.com/News/2home.htm
> Get the OzGrid Add-in
> http://www.ozgrid.com/Services/AddinExamples.htm
> If it's Excel, then it's us!



> | Hi Dave:
> |
> | I want to pass the variable (ByVal), not by using a public variable and
> then
> | reading from it later. Sorry for not being clearer.
> |
> | Tim
> |


> | > Hi Tim
> | >
> | > 'MUST be at the very top of any Standard Module.
> | > Public rCell as Range
> | >
> | > 'In the worksheet
> | > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
> | > Boolean)
> | > set rCell =Target
> | >   Cancel = True
> | >   UserForm1.Show
> | > End Sub
> | >
> | > 'In the UserForm
> | > Private Sub CommandButton1_Click(ByVal Cell as Range)
> | >     rCell  = TextBox1.Value
> | > End Sub
> | >
> | >
> | > Hope this helps
> | >
> | > --
> | > Kind Regards
> | > Dave Hawley
> | > www.MicrosoftExcelTraining.com
> | > www.OzGrid.com
> | > FREE EXCEL NEWSLETTER
> | > http://www.ozgrid.com/News/2home.htm
> | > Get the OzGrid Add-in
> | > http://www.ozgrid.com/Services/AddinExamples.htm
> | > If it's Excel, then it's us!
> | >
> | >
> | >


> | > | [Originally posted in the wrong group worksheet.functions.]
> | > |
> | > | Is there a way to pass a variable through a UserForm?
> | > |
> | > | For example, I have a UserForm with a command button and a textbox.
> When
> | I
> | > | double-click on a worksheet, show the userform.When I click on the
> | command
> | > | button in the UserForm, I want to put the value from the textbox in
> the
> | > | double-clicked cell on the worksheet.
> | > |
> | > | Here is the *pseudo*-code:
> | > |
> | > | 'In the worksheet
> | > | Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range,
Cancel
> As
> | > | Boolean)
> | > |   Cancel = True
> | > |   UserForm1.Show (Cell: = Target(1,1))
> | > | End Sub
> | > |
> | > | 'In the UserForm
> | > | Private Sub CommandButton1_Click(ByVal Cell as Range)
> | > |     Cell.Value = TextBox1.Value
> | > | End Sub
> | > |
> | > | Thanks.
> | > | Tim
> | > |
> | > |
> | > |
> | >
> | >
> |
> |



Mon, 13 Dec 2004 15:41:09 GMT  
 Passing a variable through a UserForm
Thanks Rob..exactly what I was looking for!

Tim


Quote:
> Hi Tim,

>     I'd suggest creating a Caller property for your UserForm that would
> store the range object you wanted to modify. Load the UserForm and set
this
> property to the range on the worksheet that was double-clicked before
> displaying the UserForm. Then you'll always have a reference to the
correct
> cell. Something like this:

> In the UserForm code module:
> -------------------------------------------------------------
> Private mrngCaller As Range

> Public Property Set Caller(ByRef rngNewValue As Range)
>     Set mrngCaller = rngNewValue
> End Property

> Private Sub CommandButton1_Click()
>     mrngCaller.Value = TextBox1.Text
> End Sub

> In the worksheet code module:
> ------------------------------------------------------------------
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> Boolean)
>     Load UserForm1
>     Set UserForm1.Caller = Target
>     UserForm1.Show
> End Sub

> --
> Rob Bovey, MCSE, MCSD, Excel MVP
> Application Professionals
> http://www.appspro.com/

> * Please post all replies to this newsgroup *
> * I delete all unsolicited e-mail responses *



> > Nice try but if the form is modeless then all you have to do is select
> > another worksheet before clicking the button, and your code would place
> the
> > textbox value in the cell address, but different worksheet (not the
> > Target.Parent) of the double-clicked cell.



> > > Hello Tim
> > > Here's one way to achieve this:
> > > - Declare a public variable in a standard module:
> > > Public MyCell
> > > - In you Worksheet_BeforeDoubleClick add this:
> > > Cancel = True
> > > MyCell = Target.Address(False, False)
> > > Load UserForm1
> > > UserForm1.Show
> > > - In your CommandButton put this:
> > > Private Sub CommandButton1_Click()
> > > Me.Hide
> > > ActiveSheet.Range(MyCell).Value = Me.TextBox1.Value
> > > Unload Me
> > > End Sub
> > > Works for me (Excel 2K)

> > > HTH
> > > Regards

> > > Pascal (France)



> > > > [Originally posted in the wrong group worksheet.functions.]

> > > > Is there a way to pass a variable through a UserForm?

> > > > For example, I have a UserForm with a command button and a textbox.
> When
> > I
> > > > double-click on a worksheet, show the userform.When I click on the
> > command
> > > > button in the UserForm, I want to put the value from the textbox in
> the
> > > > double-clicked cell on the worksheet.

> > > > Here is the *pseudo*-code:

> > > > 'In the worksheet
> > > > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range,
Cancel
> As
> > > > Boolean)
> > > >   Cancel = True
> > > >   UserForm1.Show (Cell: = Target(1,1))
> > > > End Sub

> > > > 'In the UserForm
> > > > Private Sub CommandButton1_Click(ByVal Cell as Range)
> > > >     Cell.Value = TextBox1.Value
> > > > End Sub

> > > > Thanks.
> > > > Tim



Mon, 13 Dec 2004 15:53:54 GMT  
 Passing a variable through a UserForm
Hi Earl: I could not get your example to work for me.


Quote:

> 'In the UserForm
> Dim mMyRange as Range

> Private Sub CommandButton1_Click()
>     mMyRange.value = TextBox1.Value
> End Sub

> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> Boolean)
>       Cancel = True
>       UserForm1.Load
>       set UserFrom1.mMyRange = Target(1,1)
>       userForm1.Show
> End Sub

> HTH,

> --
> Earl K. Takasaki

> Please reply to the group.



> [Originally posted in the wrong group worksheet.functions.]

> Is there a way to pass a variable through a UserForm?

> For example, I have a UserForm with a command button and a textbox. When I
> double-click on a worksheet, show the userform.When I click on the command
> button in the UserForm, I want to put the value from the textbox in the
> double-clicked cell on the worksheet.

> Here is the *pseudo*-code:

> 'In the worksheet
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> Boolean)
>   Cancel = True
>   UserForm1.Show (Cell: = Target(1,1))
> End Sub

> 'In the UserForm
> Private Sub CommandButton1_Click(ByVal Cell as Range)
>     Cell.Value = TextBox1.Value
> End Sub

> Thanks.
> Tim



Mon, 13 Dec 2004 15:53:12 GMT  
 Passing a variable through a UserForm
<<Not likely, but Num could be changed somewhere else before B displayed
it.>>

    It's not likely in this small example, but in real-world programs, the
possibility of multiple procedures stepping on each other by modifying the
value of a global variable they all depend on in unintended ways is the main
argument for not using global variables.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


Quote:
> Mostly a personal preference. When I am passing a value to a procedure I'd
> like to try to be more direct about it.

> For example, I'd rather use this approach:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Quote:
> Sub A()
>     Call B(Num:=5)
> End Sub

> Sub B(ByVal Num as Integer)
>     Msgbox "A passed me " & Num
> End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Quote:

> Than this:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Quote:
> Public Num as Integer

> Sub A()
>     Num = 5
>     Call B
> End Sub

> Sub B()
>     Msgbox "Num has the value of " & Num
> End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Quote:
> I find the first approach easier to follow. The second requires some
> assumptions and can be harder to track down especially if A and B exist in
> different modules. Not likely, but Num could be changed somewhere else
> before B displayed it.



Mon, 13 Dec 2004 16:05:23 GMT  
 Passing a variable through a UserForm
I use globals all the time and call them from several procedures and never
ever have a problem...<snicker snicker>
______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel

Quote:
> <<Not likely, but Num could be changed somewhere else before B displayed
> it.>>

>     It's not likely in this small example, but in real-world programs, the
> possibility of multiple procedures stepping on each other by modifying the
> value of a global variable they all depend on in unintended ways is the
main
> argument for not using global variables.

> --
> Rob Bovey, MCSE, MCSD, Excel MVP
> Application Professionals
> http://www.appspro.com/

> * Please post all replies to this newsgroup *
> * I delete all unsolicited e-mail responses *



> > Mostly a personal preference. When I am passing a value to a procedure
I'd
> > like to try to be more direct about it.

> > For example, I'd rather use this approach:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Quote:
> > Sub A()
> >     Call B(Num:=5)
> > End Sub

> > Sub B(ByVal Num as Integer)
> >     Msgbox "A passed me " & Num
> > End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Quote:

> > Than this:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Quote:
> > Public Num as Integer

> > Sub A()
> >     Num = 5
> >     Call B
> > End Sub

> > Sub B()
> >     Msgbox "Num has the value of " & Num
> > End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

- Show quoted text -

Quote:
> > I find the first approach easier to follow. The second requires some
> > assumptions and can be harder to track down especially if A and B exist
in
> > different modules. Not likely, but Num could be changed somewhere else
> > before B displayed it.



Mon, 13 Dec 2004 16:18:02 GMT  
 
 [ 19 post ]  Go to page: [1] [2]

 Relevant Pages 

1. Passing a Variable from one Userform to another Userform

2. Passing a variable through a UserForm

3. Pass variable to userform

4. Passing variables between userforms and/or normal procedures

5. Excel 97: Pass variable from userform to Sub ?

6. Passing variables or arrays to userforms

7. Passing variables or arrays to userform objects

8. Passing UserForm Variable To Class Module

9. Passing Variables between UserForm/Module

10. Passing strings/variables between userform and macro


 
Powered by phpBB® Forum Software © phpBB Group