Run simple macro from Application.Caller

[Hidden Dan]

Hello all,

I'm new here and though pretty well experienced in data mining and spreadsheets, I'm very poor in programming.

For large spreadsheets I use quite some 'Grouping' actions to make it all more compact. The Groupings are called from a button and the name of the button is such that it will tell the macro how many rows should be grouped and which one is the starting row. ('btn_20_5_H' will hide 5 rows starting at row 20 ).

Button name can be like 'btn_20_5_H and executes script below:


Sub ShowHideRows()


Dim arr


'split the calling button name into an array
' (array will be zero-based)
arr = Split(Application.Caller, "_")


'**EDIT** check array is expected size...
If UBound(arr) <> 3 Then Exit Sub


If IsNumeric(arr(1)) And IsNumeric(arr(2)) Then
With ActiveSheet '

'arr(1) determines start row
'arr(2) determines # of rows
'arr(3) determines if rows are hidden or not
.Cells(arr(1), 1).Resize(arr(2), 1).EntireRow.Hidden = (arr(3) = "H")

End With
End If
End Sub


Now I got 24 scripts that lock/unlock some cells. These cells are all 'Named Ranges' like Month01, Month02, Month03, etc. Simplified script is like below:

Sub Macro_unlock()


Application.Goto Reference:="Month03"
Selection.Locked = False
Selection.FormulaHidden = False

End Sub


I was trying to create a generic script from the above which integrates the unlock macro. A named button like 'btn_Month01' should execute the unlock for named range Month01 and 'btn_Month02' should do for named range Month02 and so on till Month12.



My original idea is something like this :

Sub LockMonth()


Dim arr as String


'split the calling button name into an array
' (array will be zero-based)
arr = Split(Application.Caller, "_")


'**EDIT** check array is expected size...
If UBound(arr) <> 2 Then Exit Sub



With ActiveSheet '

'arr(1) determines Named Range


Application.Goto Reference:=arr(1)
Selection.Locked = False
Selection.FormulaHidden = False


End With


End Sub




But unfortunately this is not correct programming. I'm not sure where to look but 'Application.Goto Reference:=arr(1)' is most probaly incorrect.

Can I get some help here to reconstruct my Application.Caller Lock script ?


Thanks

Dan

 

[Norie]

What rows are you hiding/showing with the buttons?

Are there any patterns/duplications in the code you currently have?

Where are the buttons positioned relative to the rows they show/hide?

PS Have you considered using toggle buttons?

 

[Hidden Dan]

What rows are you hiding/showing with the buttons?

Script listed on top of this topic is the script for hiding / showing rows. It works perfect and it is executed by + or - buttons. If a - button is named as btn_61_5_H it will hide rows 61-65. Plus button at row 66 will show them. This button is called btn_61_5_S .

Are there any patterns/duplications in the code you currently have?

Not as far as I know

Where are the buttons positioned relative to the rows they show/hide?

They are all positioned in column A

PS Have you considered using toggle buttons?

That would a nice feature but it's currently beyond my programming skills.


But for the lock/unlock + change color I can easily work with 2 scripts. One to lock and one to unlock, both executed by Application.Caller . Two or 24 scripts make a huge difference. The 2 scripts already work fine in my setup.

 

[Hidden Dan]

What rows are you hiding/showing with the buttons?

Are there any patterns/duplications in the code you currently have?

Where are the buttons positioned relative to the rows they show/hide?

PS Have you considered using toggle buttons?

Well,I have proceeded till next example. Which is nice for me.

Spreadsheet is password protected. In this example case I simply use "" in the script. Standard fill-in cells are yellow. Once you click 'Lock' yellow cells will turn grey and cells are locked. When clicking 'Unlock' reverse happens. Cell ranges below Jan, Feb, Mrt are called Month01, Month02, Month03.

I have 2 scripts, one for locking, one for unlocking. Buttons are called after the named range. BOTH LOCK and UNLOCK button share same name. I didn't know this was possible. I created this by first creating Lock 1 button, naming it btn_Month01, and than duplicating it. And changed macro of course. Then I freshly created Lock 2 button, named it and duplicated.


Two scripts are as below:

Lock cells :

Sub LockMonth()


ActiveSheet.Unprotect Password:=""


Dim arr As Variant


arr = Split(Application.Caller, "_")

If UBound(arr) <> 1 Then Exit Sub


With ActiveSheet.Range(arr(1))
.Locked = True
.FormulaHidden = True


With .Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -4.99893185216834E-02
.PatternTintAndShade = 0


End With
End With


ActiveSheet.Protect Password:=""


End Sub


Unlock script :

Sub UnLockMonth()


ActiveSheet.Unprotect Password:=""


Dim arr As Variant


arr = Split(Application.Caller, "_")

If UBound(arr) <> 1 Then Exit Sub


With ActiveSheet.Range(arr(1))
.Locked = False
.FormulaHidden = False


With .Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13434879
.TintAndShade = 0
.PatternTintAndShade = 0


End With
End With

ActiveSheet.Protect Password:=""


End Sub





Thank you Norie for your assistance

 

[Hidden Dan]

Update:

Buttons for lock and unlock should definitively have different names. So my buttons are called :

btn_Month01 / 02 / 03 to lock cells - they call LockMonth()
btnn_Month01 / 02 / 03 to unlock cells - these call UnLockMonth()

And Month 01 / 02 / 03 are named ranges in de spreadsheet

 

[Norie]

The reason I was asking about the position of the buttons is because of the TopLeftCell property.

TopLeftCell is a property of shapes/objects, including buttons, and as the name kind of suggests is a reference to the top left cell of the range the shape/button is positioned over.

My thinking was that this property could be used to determine which rows to hide/unhindered or cells to lock/unlock.