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]

Dan

Are you getting compile errors? Runtime errors?

Does this work?

Sub LockMonth()
Dim arr As Variant

    arr = Split(Application.Caller, "_")
    
    If UBound(arr) <> 1 Then Exit Sub

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

End Sub

 

[Hidden Dan]

Dan

Are you getting compile errors? Runtime errors?

Does this work?

Sub LockMonth()
Dim arr As Variant

    arr = Split(Application.Caller, "_")
    
    If UBound(arr) <> 1 Then Exit Sub

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

End Sub

Unfortunately it returns a type mismatch in

Error 13 : arr = Split(Application.Caller, "_")

 

[Norie]

How have you called the code when you get that error?

 

[Hidden Dan]

How have you called the code when you get that error?

I have a range called "Month01" at L4:L5. My button is called "btn_Month01". When I click button nothing happens. But when I examine script in code view and run F5 it shows 'Type mismatch (correct translation ?)

Dan

 

[Hidden Dan]

OMG, I was looking wrong ! Script works but I forgot to check real state of cells.

Actual script unlocks cells and I started with unlocked cells. In that case not much happens !


Thanks for helping me. I will continu to expand the script with additional functionality. It will be used to lock several cells when monthly data is typed in. All entered data will be fixed and background color will change also. This prevents that 'someone' else will change data afterwards.

This script allows for writing 1 macro, instead of 24 (to lock cells and to unlock).

Dan

 

[Norie]

Dan

Glad you got it sorted.

One thing to point out though, you can't really use F5 to debug code that uses Application.Caller because Application.Caller will be empty when run like that.

Better to put a breakpoint (F9) somewhere in the code and then call the code using one of the buttons that it's been assigned to.

 

[Hidden Dan]

Dan

Glad you got it sorted.

One thing to point out though, you can't really use F5 to debug code that uses Application.Caller because Application.Caller will be empty when run like that.

Better to put a breakpoint (F9) somewhere in the code and then call the code using one of the buttons that it's been assigned to.

OK, thanks for the explanation. Meanwhile I got background color change working blended in as well.

One thing in addition, do you have any idea how to add lock/unlock automatically ? I should expand button name to 'btn_Month01_L' to lock and 'btn_Month01_U' to unlock. I would need a 'Ubound <> 2' certainly. But :

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

should change with arr(2) coming from the Application.Caller



Dan

 

[Norie]

Dan

Do you have pairs of buttons that essentially reverse each others actions?

For example one locks and sets the background colour and its counterpart unlocks and clears, or changes, the background colour.

 

[Hidden Dan]

Dan

Do you have pairs of buttons that essentially reverse each others actions?

For example one locks and sets the background colour and its counterpart unlocks and clears, or changes, the background colour.

For my script for Hide / Show rows or columns I use : btn_61_5_H and btn_61_5_S as name for the 2 buttons

Something similar is in use in this project

Clicking F (btn_Month09) will lock several cells from Range "Month09". It will also change color to grey. Clicking the crossed F (btn_Month09_2) will unlock cells and change color back to yellow. I currently use 24(!) macro's to have it all functional. And above this macro opens and closes password protected spreadsheet.