Hi there,
Firstly apologies if something similar has been answered before, or i've missed something very obvious, i'm by no means a VB expert.
Background
I have a Worksheet broken down into Sections, at the top of each Section I have a Macro for Users to Show/Hide (the Rows for) that Section.
Some of the Sections however, are 'Admin' Sections, that only certain Users should be able to view, I have achieved this by utilising a Username Function and a manual list of Usernames. If the Username returned by the Function appears in the Username list then a Cell on the Sheet is set a value of 'Admin'. When a Section Macro is activated it first checks that the Cell Value = 'Admin', if so then the Section will be Hidden/Unhidden (depending on it's current state). If the Cell Value does not = 'Admin' then a Message box is displayed to state that the User does not have 'Admin' control.
If the Cell Value = 'Admin' then the code will check to see if the Rows for that particular section are Hidden, if True then the rows will be Unhidden, if False then the Rows will be Hidden. The Row ranges for each Section are held in a separate Cell in each Section as a value, "10:35" etc
Query
Is there a way to use the current Macro name as a value without entering it in the code, but just by using something like ("macroname"), and then whatever that Macro name is - that will be used ?
I would like to keep my Code as small and simple as possible and make it easy for others to setup future Section Macros, I would like to make a 'Macro' list in a Section on my sheet to act a lookup table of sorts. It would contain the Section Macro names, then whether that Section is just for 'Admin' or all users, and finally the Row range value. Hopefully I could then have single shared Macro code, and all each Section Macro would do is 'store' it's own name and then run the shared code to 'look up' the stored name value in this list and then offset from that 'looked up' Cell (although without actually selecting it) to analyse the subsequent values in that Macro's row ('Admin' or 'All Users', and the Row range value to show/hide).
At least that's the plan, partly depending if you can use the Macro name as a variable in the code. If not I guess I can 'hardcode' it in instead.
I also need to know if it's possible, and not unnecessary, to code the 'look up' or Find as i've detailed it.
As it stands my code is as follows...
Sub Toggle_Section01()
If Range("I3").Value = "Admin" Then
If Range(range("C49").Value).EntireRow.Hidden = True Then
Range(Range("C49").Value).EntireRow.Hidden = False
Else
Range(Range("C49").Value).EntireRow.Hidden = True
End If
Else
Run ("MsgBox_LimitedControl")
End If
End Sub
Where Cell I3 holds the Value "Admin" or "Limit", and Cell C49 holds the Row range ("39:47") for that section.
What I would like is something like this...
Sub Toggle_Section01()
Store macro name
Run (Shared_SectionCode)
End Sub
Sub Shared_SectionCode()
Lookup Stored Macro Name in Sheet list
Cannot Find - Msgbox to state this and exit
Found name - offset from that cell address to the next column and see If value = 'Admin'
If value does = 'Admin' - check if cell I3 = 'Admin'
If Cell I3 does not = 'Admin' - Msgbox to state this and exit
If Cell I3 does = 'Admin' then offset again to next column in the list for the Row range
If Row range Cell is blank - then Msgbox to state this and exit
Check if Row range is hidden
If it is hidden - unhide it - (end)
If it is not hidden - hide it - (end)
If value does not = 'Admin' - check if cell value = 'Limit'
If Cell I3 does not = 'Limit' - Msgbox to state this (should only be an error at this point) and exit
If Cell I3 does = 'Limit' then offset again to next column in the list for the Row range
If Row range Cell is blank - then Msgbox to state this and exit
Check if Row range is hidden
If it is hidden - unhide it - (end)
If it is not hidden - hide it - (end)
End Sub
Any help, or alternative ideas, on this would be greatly apprieciated.