Hi

There is a Command Button on my worksheet that I only want selected users to
be able to use.

Is there some simple code I can put at the start of the Macro so that unless
"Yes" is typed in to cell A1, the Macro doesnt activate when the button is
pressed

Many thanks

Something like the following may suffice:

Sub ButtonClick()
If StrComp(ThisWorkbook.Worksheets("Sheet1").Range("A 1").Value, "yes",
vbTextCompare) < 0 Then
' cell A1 is not 'yes'.
Exit Sub
End If
''''''''''''''''''''''''''
' rest of your code here
''''''''''''''''''''''''''
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"dave caizley" wrote in message
...
Hi

There is a Command Button on my worksheet that I only want selected users
to
be able to use.

Is there some simple code I can put at the start of the Macro so that
unless
"Yes" is typed in to cell A1, the Macro doesnt activate when the button is
pressed

Many thanks

hi

i've worked with this one:

Dim flag As Boolean
Private Sub CommandButton1_Click()
If LCase(Range("a1")) = "yes" Then flag = True: yourmacro Else flag =
False: yourmacro
End Sub

Sub yourmacro()
MsgBox flag
End Sub

--
Regards,

Halim



"dave caizley" wrote:

Hi

There is a Command Button on my worksheet that I only want selected users to
be able to use.

Is there some simple code I can put at the start of the Macro so that unless
"Yes" is typed in to cell A1, the Macro doesnt activate when the button is
pressed

Many thanks

Hi Chip

Thanks for the reply

Being a novice to macros I think I have not fully understood what I need to
have at the start of the Macro. I guess its the sub part being repeated but
hopefully you can guide me. In case I havent made my self fully clear, I just
want the command button to be inactive unless the correct input has been
made. I dont want it to run into a debugger programme that will confuse the
users

Here is my macro including your content

Sub Review5()
'
' review5 Macro
' Macro recorded 04/06/2007 by DCaizley
'
Sub ButtonClick()
If StrComp(ThisWorkbook.Worksheets("Sheet1").Range("A 1").Value, "yes",
vbTextCompare) < 0 Then
'cell A1 is not 'yes'.
Exit Sub
'End If
Rows("95:102").Select
Selection.EntireRow.Hidden = False
Rows("94:94").Select
Selection.EntireRow.Hidden = True
Range("L30").Select
ActiveCell.FormulaR1C1 = " Limit Changes &"
Range("e95").Select
End Sub

Hope you can put me straight

Many thanks

"Chip Pearson" wrote:

Something like the following may suffice:

Sub ButtonClick()
If StrComp(ThisWorkbook.Worksheets("Sheet1").Range("A 1").Value, "yes",
vbTextCompare) < 0 Then
' cell A1 is not 'yes'.
Exit Sub
End If
''''''''''''''''''''''''''
' rest of your code here
''''''''''''''''''''''''''
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"dave caizley" wrote in message
...
Hi

There is a Command Button on my worksheet that I only want selected users
to
be able to use.

Is there some simple code I can put at the start of the Macro so that
unless
"Yes" is typed in to cell A1, the Macro doesnt activate when the button is
pressed

Many thanks

The code I posted allows the command button to be active and enabled all the
time. If, however, Range("A1") is not "yes", the code does nothing, existing
immediately. If, on the other hand, you want to completely disable the
command button, use code like the following in the Sheet module of the
worksheet that contains the button.

Private Sub CommandButton1_Click()
MsgBox "Hello World"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If StrComp(Target.Value, "yes", vbTextCompare) = 0 Then
Me.CommandButton1.Enabled = True
Else
Me.CommandButton1.Enabled = False
End If
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)





"dave caizley" wrote in message
...
Hi Chip

Thanks for the reply

Being a novice to macros I think I have not fully understood what I need
to
have at the start of the Macro. I guess its the sub part being repeated
but
hopefully you can guide me. In case I havent made my self fully clear, I
just
want the command button to be inactive unless the correct input has been
made. I dont want it to run into a debugger programme that will confuse
the
users

Here is my macro including your content

Sub Review5()
'
' review5 Macro
' Macro recorded 04/06/2007 by DCaizley
'
Sub ButtonClick()
If StrComp(ThisWorkbook.Worksheets("Sheet1").Range("A 1").Value, "yes",
vbTextCompare) < 0 Then
'cell A1 is not 'yes'.
Exit Sub
'End If
Rows("95:102").Select
Selection.EntireRow.Hidden = False
Rows("94:94").Select
Selection.EntireRow.Hidden = True
Range("L30").Select
ActiveCell.FormulaR1C1 = " Limit Changes &"
Range("e95").Select
End Sub

Hope you can put me straight

Many thanks

"Chip Pearson" wrote:

Something like the following may suffice:

Sub ButtonClick()
If StrComp(ThisWorkbook.Worksheets("Sheet1").Range("A 1").Value, "yes",
vbTextCompare) < 0 Then
' cell A1 is not 'yes'.
Exit Sub
End If
''''''''''''''''''''''''''
' rest of your code here
''''''''''''''''''''''''''
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"dave caizley" wrote in message
...
Hi

There is a Command Button on my worksheet that I only want selected
users
to
be able to use.

Is there some simple code I can put at the start of the Macro so that
unless
"Yes" is typed in to cell A1, the Macro doesnt activate when the button
is
pressed

Many thanks