Solved: Macro can only be executed if specific cells are selected

[coliervile]

I want a macro that will only allow it to run if specific cell/s are selected (or active) in the worksheet. For example: if cell J12 is selected the user can click on the menu bar to execute a macro. If cell J11 is selected the user can not execute any macros from the menu bar. The macros are executed from various menu bars in the workbook.
Thanks for your help.


Here's an example of a macro:

[vba]Sub Single1B()
Sheets("ScoreCard").Select
ActiveCell.Offset(1, 1).Select
Sheets("Symbols").Range("K16:M17").Copy ActiveCell
Sheets("ScoreCard").Select
ActiveCell.Offset(-1, -1).Select
End Sub[/vba]

Example of a menu bar:

[VBA]
Sub RunnerMakeOuts()
' This procedure creates a new temporary toolbar.
Dim ComBar As CommandBar, ComBarContrl As CommandBarControl
On Error GoTo ErrorHandler
' Create a new floating toolbar and make it visible.
On Error Resume Next
'Delete the toolbar if it already exists
CommandBars("Way Runners Make Outs").Delete
Set ComBar = CommandBars.Add(Name:="Way Runners Make Outs", Position:= _
msoBarFloating, Temporary:=True)
ComBar.Visible = True
' Create a button with text on the bar and set some properties.

Set ComBarC With ComBarContrl
.Caption = "CS 2B"
. .TooltipText = "Caught Stealing 2nd"
.
.BeginGroup = True
End With

Set ComBarC With ComBarContrl
.Caption = "CS 3B"
. .TooltipText = "Caught Stealing 3rd"
.
.BeginGroup = True
End With

Set ComBarC With ComBarContrl
.Caption = "CS Home"
. .TooltipText = "Caught Stealing Home"
.
.BeginGroup = True
End With

Set ComBarC With ComBarContrl
.Caption = "DP 1st"
. .TooltipText = "Double Play First"
.
.BeginGroup = True
End With

Set ComBarC With ComBarContrl
.Caption = "DP 2nd"
. .TooltipText = "Double Play Second"
.
.BeginGroup = True
End With

Set ComBarC With ComBarContrl
.Caption = "DP 3rd"
. .TooltipText = "Double Play Third"
.
.BeginGroup = True
.BeginGroup = True
End With

Set ComBarC With ComBarContrl
.Caption = "DP Home"
. .TooltipText = "Double Play Home"
.
.BeginGroup = True
End With

Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub
[/VBA]

[Simon Lloyd]

The easiest way would probably be to have all your cells locked apart from those you want the user to have access to then in you macro you can check if the cell is locked, the beauty of this is you do not have to protect the worksheet for it to work!
[vba]
Sub locked()
If ActiveCell.locked = False Then
MsgBox "You can use this cell"
Else
MsgBox "You cannot use this cell"
End If
End Sub
[/vba]

[coliervile]

Simon Lloyd thanks for your suggestion. Since this is a scorecard I want the user to type in the other cells, but don't want them to inadvertently execute a macro either.

[Simon Lloyd]

Yes thats the point a locked cell does nothing unless the worksheet is protected, however Excel can still pick out the property, so using my logic all cells could be accessed but theones you decide to have the locked or unlocked status could run the macro!

[coliervile]

Let me see if I understand you idea. I lock the cells that I want only the macro to be run in. When I run your macro it sees if the cell is locked and if it is then the macro runs and if it isn't locked then the macro doesn't run??? Where would your macro get placed...in the macro itself or the tool bar menu???

[Simon Lloyd]

You can of course choose to have your macro run when a selected cell is locked or when a selected cell is unlocked:
[VBA]
Sub locked()
If ActiveCell.locked = False Then
'Run your macro if cell is not locked
Else
'Run your macro if cell is locked
End If
End Sub
[/VBA]Replace Run Your macro.... with Call and then your macro name. The code can be assigned to a button or you can run it when the cell is selected automatically like this:
[VBA]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.locked = False Then
MsgBox "You can use this cell"
Else
MsgBox "You cannot use this cell"
End If
End Sub
[/VBA]the above goes in the worksheet code module and runs as soon as the cell is selected. The code below also goes in the worksheet module but runs after the cell has had an entry made.
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.locked = False Then
MsgBox "You can use this cell"
Else
MsgBox "You cannot use this cell"
End If
End Sub[/VBA]

[Bob Phillips]

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "G1,H5,K7,M3,N11"

With Application.Commandbars("Way Runners Make Outs")

.Controls("CS 3B").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
'etc.
End With
End Sub
[/vba]

[coliervile]

Bob thanks for your suggestion. I trying to figure it out though....I'm a Rookie. Where did you get this range "G1,H5,K7,M3,N11"?

I've got a total of 6 toolbars named; "Updating the Scorecard", "Way Runners Make Outs", "How Batters Make Outs on the Ground", "How Batters Make Outs in the Air", "How Batter's get on Base", "How Runners Advance on Base". I also have a total of 85 macro on these various toolbar menus.

If I understand your coding for "Way Runners Make Outs" it would look like this:

[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "J12,S12,AB12,AK12,AT112,BC12,BL12,BU12,CD12"
'and so on until all of the worksheet range is complete'

'I would have a With statement for each toolbar'
With Application.Commandbars("Way Runners Make Outs")

.Controls("CS 2B").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

.Controls("CS 3B").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

.Controls("CS Home").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

.Controls("DP 1st").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

.Controls("DP 2nd").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

.Controls("DP 3rd").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

.Controls("DP Home").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

'etc.
End With
End Sub
[/vba]

[Bob Phillips]

Bob thanks for your suggestion. I trying to figure it out though....I'm a Rookie. Where did you get this range "G1,H5,K7,M3,N11"?

I just plucked them out the air, to give you an example of the technique.

If I understand your coding for "Way Runners Make Outs" it would look like this:

[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "J12,S12,AB12,AK12,AT112,BC12,BL12,BU12,CD12"
'and so on until all of the worksheet range is complete'

'I would have a With statement for each toolbar'
With Application.Commandbars("Way Runners Make Outs")

.Controls("CS 2B").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
.Controls("CS 3B").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
.Controls("CS Home").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
.Controls("DP 1st").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
.Controls("DP 2nd").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
.Controls("DP 3rd").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
.Controls("DP Home").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

'etc.
End With
End Sub
[/vba]

Yes you have got the idea, but if I had that many I would set a boolean for the cell intersection, for readability and efficiency

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "J12,S12,AB12,AK12,AT112,BC12,BL12,BU12,CD12"
'and so on until all of the worksheet range is complete'
Dim CellActive As Boolean

'I would have a With statement for each toolbar'
CellActive = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
With Application.CommandBars("Way Runners Make Outs")

.Controls("CS 2B").Enabled = CellActive
.Controls("CS 3B").Enabled = CellActive
.Controls("CS Home").Enabled = CellActive
.Controls("DP 1st").Enabled = CellActive
.Controls("DP 2nd").Enabled = CellActive
.Controls("DP 3rd").Enabled = CellActive
.Controls("DP Home").Enabled = CellActive
'etc.
End With
End Sub
[/vba]

You would of course (or at least I presume), want a different range for each toolbar. If all buttons on the toolbar are disabled for a range, it would be better IMO to hide all of the other toolbars, and only make the relevant one visible.

[Simon Lloyd]

Lol, very exacting Bob.....i still like my version tho'

[coliervile]

I'm getting a Compile Error: Expected: end of statement in my "Const WS_RANGE As String" can't figure out why???

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "J12,S12,AB12,AK12,AT12,BC12,BL12,BU12, _
CD12,J21,S21,AB21,AK21,AT21,BC21,BL21,BU21,CD21, _
J30,S30,AB30,AK30,AT30,BC30,BL30,BU30,CD30,J39,S39, _
AB39,AK39,AT39,BC39,BL39,BU39,CD39,J48,S48,AB48, _
AK48,AT48,BC48,BL48,BU48,CD48,J57,S57,AB57,AK57,AT57, _
BC27,BL57,BU57,CD57,J66,S66,AB66,AK66,AT66,BC66,BL66, _
BU66,CD66,J75,S75,AB75,AK75,AT75,BC75,BL75,BU75,CD75, _
J84,S84,AB84,AK84,AT84,BC84,BL84,BU84,CD84,J93,S93, _
AB93,AK93,AT93,BC93,BL93,BU93,CD93,J102,S102,AB102, _
AK102,AT102,BC102,BL102,BU102,CD102,J111,S111,AB111,AK111,AT111,BC111,BL111 ,BU111,CD111"
'and so on until all of the worksheet range is complete'

'I would have a With statement for each toolbar'
With Application.CommandBars("Way Runners Make Outs")

.Controls("CS 2B").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
.Controls("CS 3B").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
.Controls("CS Home").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
.Controls("DP 1st").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
.Controls("DP 2nd").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
.Controls("DP 3rd").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
.Controls("DP Home").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

'etc.
End With
End Sub[/VBA]

[coliervile]

I got by the first part with this and now I get this error message-

Run-time error '1004':
Method 'Range' of object'_Worksheet' failed at this location in red-

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "J12,S12,AB12,AK12,AT12,BC12,BL12,BU12," & _
"CD12,J21,S21,AB21,AK21,AT21,BC21,BL21,BU21,CD21," & _
"J30,S30,AB30,AK30,AT30,BC30,BL30,BU30,CD30,J39,S39," & _
"AB39,AK39,AT39,BC39,BL39,BU39,CD39,J48,S48,AB48," & _
"AK48,AT48,BC48,BL48,BU48,CD48,J57,S57,AB57,AK57,AT57," & _
"BC27,BL57,BU57,CD57,J66,S66,AB66,AK66,AT66,BC66,BL66," & _
"BU66,CD66,J75,S75,AB75,AK75,AT75,BC75,BL75,BU75,CD75," & _
"J84,S84,AB84,AK84,AT84,BC84,BL84,BU84,CD84,J93,S93," & _
"AB93,AK93,AT93,BC93,BL93,BU93,CD93,J102,S102,AB102," & _
"AK102,AT102,BC102,BL102,BU102,CD102,J111,S111,AB111," & _
"AK111,AT111,BC111,BL111,BU111,CD111"
'and so on until all of the worksheet range is complete'

'I would have a With statement for each toolbar'
With Application.CommandBars("Way Runners Make Outs")

.Controls("CS 2B").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

.Controls("CS 3B").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

.Controls("CS Home").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

.Controls("DP 1st").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

.Controls("DP 2nd").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

.Controls("DP 3rd").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

.Controls("DP Home").Enabled = Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing

'etc.
End With
End Sub[/VBA]

[Bob Phillips]

The string is too long Charlie.

Try this approach

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim aryRows As Variant
Dim aryCols As Variant
Dim ActiveCell As Boolean

aryRows = Array(12, 21, 30, 39, 48, 57, 66, 75, 84, 93, 102, 111)
aryCols = Array(10, 19, 28, 37, 46, 55, 64, 73, 82)

ActiveCell = Not (IsError(Application.Match(Target.Row, aryRows, 0))) And _
Not (IsError(Application.Match(Target.Column, aryCols, 0)))
'and so on until all of the worksheet range is complete'

'I would have a With statement for each toolbar'
With Application.CommandBars("Way Runners Make Outs")

.Controls("CS 2B").Enabled = ActiveCell
.Controls("CS 3B").Enabled = ActiveCell
.Controls("CS Home").Enabled = ActiveCell
.Controls("DP 1st").Enabled = ActiveCell
.Controls("DP 2nd").Enabled = ActiveCell
.Controls("DP 3rd").Enabled = ActiveCell
.Controls("DP Home").Enabled = ActiveCell
'etc.
End With
End Sub
[/vba]

[Bob Phillips]

A slight variation using column letters

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim aryRows As Variant
Dim aryCols As Variant
Dim ActiveCell As Boolean

aryRows = Array(12, 21, 30, 39, 48, 57, 66, 75, 84, 93, 102, 111)
aryCols = Array("J", "S", "AB", "AK", "AT", "BC", "BL", "BU", "CD")

ActiveCell = Not (IsError(Application.Match(Target.Row, aryRows, 0))) And _
Not (IsError(Application.Match(Left(Target.Address(, False), InStr(Target.Address(, False), "$") - 1), aryCols, 0)))
'and so on until all of the worksheet range is complete'

'I would have a With statement for each toolbar'
With Application.CommandBars("Way Runners Make Outs")

.Controls("CS 2B").Enabled = ActiveCell
.Controls("CS 3B").Enabled = ActiveCell
.Controls("CS Home").Enabled = ActiveCell
.Controls("DP 1st").Enabled = ActiveCell
.Controls("DP 2nd").Enabled = ActiveCell
.Controls("DP 3rd").Enabled = ActiveCell
.Controls("DP Home").Enabled = ActiveCell
'etc.
End With
End Sub
[/vba]

[coliervile]

Bob I did think that the string would be to long for the macro. I did input your code, but it still allows access to all of the worksheet cells from the "Way Runners Make Outs" toolbar?

[Bob Phillips]

Not for me it doesn't.

[coliervile]

Bob both of your codes worked. I had made a copy of the worksheet "ScoreCard" and renamed it something different. I'm not certain, but all of the macro have the same coding that refers to the "ScoreCard" worksheet.

[VBA]Sub Single1B()
Sheets("ScoreCard").Select
ActiveCell.Offset(1, 1).Select
Sheets("Symbols").Range("K16:M17").Copy ActiveCell
Sheets("ScoreCard").Select
ActiveCell.Offset(-1, -1).Select
End Sub[/VBA]

Would that be enough to not make your coding work on the worksheet COPY I made? If it does could I change the coding from this "Sheets("ScoreCard").Select" to this ActiveSheet.Select ?

[Bob Phillips]

I'm not really sure what you are saying here Charlie, but I do know that ActiveSheet.Select is akin to tautology, it is saying select the sheet that is currently selected.

[coliervile]

I've figured it out Bob. Thank you once again for all of your help have a great evening and enjoy the rest of your weekend.

[Bob Phillips]

Great Charlie. I must say that I like this method for checking a large number of cells. It only works where there is a pattern, but it is much neater than the MOD Column Mod Row technique.