Deactivating a Forms macro button based on a worksheet condition?
 

Hello

I am looking for a way I can deactivate/disable a Forms style macro
assigned button on a worksheet if a certain condition on the worksheet
exists.

eg. I only want the macro button to be active if the value in cell A1
is TRUE.

I know some of you may know that a more elegant solution or the only
solution exists if I instead used a Command Toolbox style button
assigned to the macro. However, I am unfamiliar with and have never
used these types of buttons before so if you are to offer assistance,
I may need a bit more help with it.

NOTE: The reason why I am trying to do this is to get around another
Excel problem that I think i harder to solve and has been of much
annoyance. I have a well laid out worksheet that features adjacent
and dedicated cell ranges for both pivot tables and the data ranges
they feed on as well as a range of other formulas that are based on
the pivot tables and other cells. Ideally, I DO NOT want the Pivot
Table to automatically enlarge/reduce in size based on what values are/
aren not in the associated data range.

eg. I only want the pivot table to provide infomation on Bill, Bob,
Jane and Sally and NOT anyone else, even if Fred, Tony, Anne, Mary or
anyother infinite number of names appear in the data source. Nor do I
want the pivot tabel to reduce in size if either or all of Bill, Bob,
Jane and Sally's names DO NOT appear in the data source range.
Unvalidated data is usually copied from an extrnal source and pasted
in to the reserved pivot table data ranges on the worksheet.

I know check the drop down menus to ONLY show data on Bill, Bob, Jane
and Sally but by default, all new field values enter the pivot table
as checked and any refereshing of the table will enlarge the table if
new previously unseen/unchecked field values are in the data source.
By preventing the pivot tables from refershing if "unvalidated"/junk
data exists in the data ranges, I can prevent the curse of the auto
enlarging pivot tablles and prevent them form overwiting the cells
below and to the right of them. Would also apprecuate any help
dealing with this probelm directly.

Cheers

Ace70

Deactivating a Forms macro button based on a worksheet condition?
 

One way is to put the following line as the first line in the macro. HTH
Otto
If Range("A1").Value<TRUE Then Exit Sub
"Ace70" wrote in message
oups.com...
Hello

I am looking for a way I can deactivate/disable a Forms style macro
assigned button on a worksheet if a certain condition on the worksheet
exists.

eg. I only want the macro button to be active if the value in cell A1
is TRUE.

I know some of you may know that a more elegant solution or the only
solution exists if I instead used a Command Toolbox style button
assigned to the macro. However, I am unfamiliar with and have never
used these types of buttons before so if you are to offer assistance,
I may need a bit more help with it.

NOTE: The reason why I am trying to do this is to get around another
Excel problem that I think i harder to solve and has been of much
annoyance. I have a well laid out worksheet that features adjacent
and dedicated cell ranges for both pivot tables and the data ranges
they feed on as well as a range of other formulas that are based on
the pivot tables and other cells. Ideally, I DO NOT want the Pivot
Table to automatically enlarge/reduce in size based on what values are/
aren not in the associated data range.

eg. I only want the pivot table to provide infomation on Bill, Bob,
Jane and Sally and NOT anyone else, even if Fred, Tony, Anne, Mary or
anyother infinite number of names appear in the data source. Nor do I
want the pivot tabel to reduce in size if either or all of Bill, Bob,
Jane and Sally's names DO NOT appear in the data source range.
Unvalidated data is usually copied from an extrnal source and pasted
in to the reserved pivot table data ranges on the worksheet.

I know check the drop down menus to ONLY show data on Bill, Bob, Jane
and Sally but by default, all new field values enter the pivot table
as checked and any refereshing of the table will enlarge the table if
new previously unseen/unchecked field values are in the data source.
By preventing the pivot tables from refershing if "unvalidated"/junk
data exists in the data ranges, I can prevent the curse of the auto
enlarging pivot tablles and prevent them form overwiting the cells
below and to the right of them. Would also apprecuate any help
dealing with this probelm directly.

Cheers

Ace70

Deactivating a Forms macro button based on a worksheet condition?
 

hi,
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1").Value = True Then
CommandButton1.Enabled = True
Else
CommandButton1.Enabled = False
End If
End Sub

this will disable/enable the button on the sheet.

regards
FSt1

"Ace70" wrote:

Hello

I am looking for a way I can deactivate/disable a Forms style macro
assigned button on a worksheet if a certain condition on the worksheet
exists.

eg. I only want the macro button to be active if the value in cell A1
is TRUE.

I know some of you may know that a more elegant solution or the only
solution exists if I instead used a Command Toolbox style button
assigned to the macro. However, I am unfamiliar with and have never
used these types of buttons before so if you are to offer assistance,
I may need a bit more help with it.

NOTE: The reason why I am trying to do this is to get around another
Excel problem that I think i harder to solve and has been of much
annoyance. I have a well laid out worksheet that features adjacent
and dedicated cell ranges for both pivot tables and the data ranges
they feed on as well as a range of other formulas that are based on
the pivot tables and other cells. Ideally, I DO NOT want the Pivot
Table to automatically enlarge/reduce in size based on what values are/
aren not in the associated data range.

eg. I only want the pivot table to provide infomation on Bill, Bob,
Jane and Sally and NOT anyone else, even if Fred, Tony, Anne, Mary or
anyother infinite number of names appear in the data source. Nor do I
want the pivot tabel to reduce in size if either or all of Bill, Bob,
Jane and Sally's names DO NOT appear in the data source range.
Unvalidated data is usually copied from an extrnal source and pasted
in to the reserved pivot table data ranges on the worksheet.

I know check the drop down menus to ONLY show data on Bill, Bob, Jane
and Sally but by default, all new field values enter the pivot table
as checked and any refereshing of the table will enlarge the table if
new previously unseen/unchecked field values are in the data source.
By preventing the pivot tables from refershing if "unvalidated"/junk
data exists in the data ranges, I can prevent the curse of the auto
enlarging pivot tablles and prevent them form overwiting the cells
below and to the right of them. Would also apprecuate any help
dealing with this probelm directly.

Cheers

Ace70

Deactivating a Forms macro button based on a worksheet condition?
 

Thanks for the suggestions.
I got the first suggestion working as I can easily make the change to
the macro script (I have little paractical VB experience). However, I
am now thinking I need to go one better and get the same thing
happening on a CommandButton from the Controls Toolbox. I searched on
this forum and found some very nice things you can do with the
CommandButtons that you can't do with the the Forms buttons.
eg. You can disable the CommandButton, the text on it can be greyed
out and display a custom message alerting the user to the reason why
the button is disabled. Here is some script that I found that does
this (I don't fully understand it but i can get it to work if i just
copy and paste):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCellsToCheck As Range
Set myCellsToCheck = Me.Range("a1,b2")

If Intersect(Target, myCellsToCheck) Is Nothing Then
Exit Sub
End If

'Top one is for the Forms toolbar
' bottom one used the control toolbox toolbar
'With Me.Buttons("button 2")
With Me.CommandButton1
Select Case Application.CountA(myCellsToCheck)
Case Is = 0:
.Enabled = True
.Caption = "click me to run macro"
Case Is = 1
.Enabled = False
.Caption = "Clear other cell" & vbLf & "before
running"
Case Is = 2
.Enabled = False
.Caption = "Clear both cells " &
myCellsToCheck.Address(0, 0) _
& vbLf & " before clicking"
End Select
End With

End Sub

Now, I am hoping to maybe combine the cool greying out/custom message
you can get on the button above, with the suggested disable/enable
script based on A1 being TRUE or not suggested below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1").Value = True Then
CommandButton1.Enabled = True
Else
CommandButton1.Enabled = False
End If
End Sub

this will disable/enable the button on the sheet.

regards
FSt1

I do need to also learn how to assign a macro to a CommandButton. I
know you are meant to assign the macro to the event on click feature
but I don't know the syntax to do that. I do knwo that a line or two
needs to be added.....

Private Sub CommandButton1_Click()
[inbetween here!!!!!!]
End Sub

....when you double click the CommandButton.

Any further help much appreciated.

Cheers

Ace70