I have a userform that contains windows media player. I want to set
CommandButton4 to take the value of the formula in cell A2 of the active
sheet and set it as the value of variable named 'inset'. I'm finding the VBA
code on this much harder to write than I thought it would be. Any ideas
please, anyone?

Hi Teepee

I am a bit confused by your request but I think that it as simple as:

Private Sub CommandButton4_Click()
inset = Range("A2").Value
MsgBox ("The value of cell A2 is: " & inset)
End Sub

Regards
Shaun


--
ShaunM
------------------------------------------------------------------------
ShaunM's Profile: http://www.excelforum.com/member.php...o&userid=18610
View this thread: http://www.excelforum.com/showthread...hreadid=497673

"ShaunM" wrote

I am a bit confused by your request but I think that it as simple as:

Private Sub CommandButton4_Click()
inset = Range("A2").Value
MsgBox ("The value of cell A2 is: " & inset)
End Sub

Thanks Shaun, that's kind of you to respond. I think the problem with your
suggestion might be that the variable is only then available within the
userform itself whereas I need to refer to the variable within other macros.
Do I not need another approach if the variable is to be generally available
elsewhere in the spreadsheet?

All you need to do is use the example above, except you need to add a Module to the project and declare the variable as public, like so:

Public inset As Integer

To avoid doubt it would probably be best to refer to the variable in code as
Module1.inset (replace 'Module1' with the module name if you change it).

Regards,
BizMark

"BizMark" wrote

All you need to do is use the example above, except you need to add a
Module to the project and declare the variable as public, like so:

Public inset As Integer

Worked a treat. Many thanks to you both

tp