UDF to Hide/Unhide worksheets and return a string

[JBC007]

Hi,

This is driving me crazy!

The idea...

The user selects a value in a drop-down list, which triggers some sheets to be hidden-unhidden, and fills-up a cell with a message of the outcome.
I want to use a single UDF to which I pass variables as needed (i.e. re-use the UDF for each sheet to hide-unhide).

The setup...

In worksheet "Module", the user clicks on cell C29 (named: "StatusSelected") where there is a drop-down list.
This drop-down list contains 2 items to choose from: "StatusEnabled" and "StatusDisabled".

In cell B36, I have a formula which gets me the active name of a specific sheet based on some other things which happen. For instance, the cell shows "Data Access", which is the name of the sheet I want to be able to hide-unhide when the user selects an item in the drop-down list.
In cell C36, I placed my UDF which uses both C29 (aka "StatusSelected") and C36 to get the name of the actual sheets to hide/unhide.

The B36 and C36 couple is repeated (in other cells) for several sheets I want to hide/unhide at the same time that C29 changes.

The point...

I need to be able to pass the name of the tab (i.e. "TabName") as a variable. In other words, the name of the tab cannot be encoded in the UDF.

The problem...

The UDF returns the proper messages depending on what item is selected by the user... but... the sheet targeted is not affected by the choice of item from the drop-down list.
I could verify that "TabName" is properly assigned, but the " Sheets(TabName).Visible = xlSheetVeryHidden " does not seem to reach the tab or, in any case, it has no effect on the tab.

The UDF call in C36...
=MODULE_Activation(B36,Modules_DataAccess,Global_ModuleEnabled,Global_ModuleDisabled)

The UDF...

I placed it in the "Module1" since I would like to have all my UDFs in a single location (if possible).

Function MODULE_Activation(TabName As String, StatusSelected As String, StatusEnabled As String, StatusDisabled As String)

' If the user selected either StatusEnabled or StatusDisabled in the drop-down list...
If StatusSelected = StatusEnabled Or StatusSelected = StatusDisabled Then

' If the user selected StatusEnabled in the drop-down list...
If StatusSelected = StatusEnabled Then

' Make the worksheet "Visible"
Sheets(TabName).Visible = xlSheetVisible

' Advise of the new status as "Visible"
MODULE_Activation = "Tab """ & TabName & """ is visible and related functionalities are activated"

' If the user selected StatusDisabled in the drop-down list...
Else

' Make the following worksheet "Hidden"
' To keep the integrity of the template, use "xlSheetVeryHidden"
' This removes the possibility to unhide via the User Interface
' Alternatively, use "xlSheetHidden" to be able to unhide via the User Interface
Sheets(TabName).Visible = xlSheetVeryHidden

' Advise of the new status as "Hidden"
MODULE_Activation = "Tab """ & TabName & """ is hidden and related functionalities are disactivated"

End If

' If the user did not select either StatusEnabled or StatusDisabled in the drop-down list...
Else

' Advise that the status selected is not valid (not equal to either StatusEnabled or StatusDisabled)
MODULE_Activation = "The status selected is not valid"

End If

End Function


Any help to make it work would be greatly appreciated...
In advance, thank you!

 

[JBC007]

A better view of the UDF:

Function MODULE_Activation(TabName As String, StatusSelected As String, StatusEnabled As String, StatusDisabled As String)

' If the user selected either StatusEnabled or StatusDisabled in the drop-down list...​

If StatusSelected = StatusEnabled Or StatusSelected = StatusDisabled Then

​

' If the user selected StatusEnabled in the drop-down list...​

If StatusSelected = StatusEnabled Then

​

' Make the worksheet "Visible"
​

Sheets(TabName).Visible = xlSheetVisible

​

' Advise of the new status as "Visible"​

MODULE_Activation = "Tab """ & TabName & """ is visible and related functionalities are activated"
​

' If the user selected StatusDisabled in the drop-down list...
​

Else
​

​

' Make the following worksheet "Hidden"
' To keep the integrity of the template, use "xlSheetVeryHidden"
' This removes the possibility to unhide via the User Interface
' Alternatively, use "xlSheetHidden" to be able to unhide via the User Interface
Sheets(TabName).Visible = xlSheetVeryHidden
​

​

' Advise of the new status as "Hidden"
MODULE_Activation = "Tab """ & TabName & """ is hidden and related functionalities are disactivated"​

​

End If
​

' If the user did not select either StatusEnabled or StatusDisabled in the drop-down list...
Else
​

' Advise that the status selected is not valid (not equal to either StatusEnabled or StatusDisabled)
MODULE_Activation = "The status selected is not valid"

​

End If
​

End Function

 

[Rosen]

Essentially you cannot do what your trying to do. Not using a UDF. UDFs cannot "do" anything except return a value. I would suggest using an ActiveX object like a Combo Box and use the Change event to run your code and update your C36 cell.

 

[JBC007]

Thank you Rosen.

Actually, I tried as well to post the following Private Sub in the worksheet where the user selects from the drop-down list.
Then I listed a number of sheets to make visible or hide.
It workded fine.

But I have 2 problems which may create some issues if my users change the worksheets names or the value in the drop-down list (and do not change the VBA):
1) I don't know how to pass the sheet names as variables (e.g. TabName) instead of fixed values such as "Data Access".
2) I don't know how to pass the "Enabled" and "Disabled" values as variables (e.g. StatusEnabled or StatusDisabled)

Any idea how to pass these as variables instead of fixed values?
Thanks...

Private Sub Worksheet_Change(ByVal Target As Range)

' If the event (i.e. Worksheet_Change) is occuring on cell StatusSelected...
If Target.Address = Me.Range("StatusSelected").Address Then

' If the user selected either "Enabled" or "Disabled" in the drop-down list of that cell...​

If Target.Value = "Enabled" Or Target.Value = "Disabled" Then
​

' If the user selected "Enabled" in the drop-down list...
' Make the following worksheets "Visible"
If Target.Value = "Enabled" Then

​

' Tab "Data Access"
Sheets("Data Access").Visible = xlSheetVisible

' Tab "Other Tab"
Sheets("Other Tab").Visible = xlSheetVisible

​

' If the user selected "Disabled" in the drop-down list...
' Make the following worksheets "Hidden"
Else

​

' Tab "Data Access"
Sheets("Data Access").Visible = xlSheetVeryHidden

' Tab "Other Tab"
Sheets("Other Tab").Visible = xlSheetVeryHidden
​

End If
​

End If
​

End If

End Sub

 

[Michael M]

Consider using the Sheet.codename instead of the sheet name.
While the Sheet name can change the codename will always be the same.

 If ActiveSheet.CodeName <> "Sheet8" then
your code

 

[JBC007]

Thanks MichaelM.

Yes, using the codename would be a way, but it is still less dynamic than using variables.

In the "Private Sub Worksheet_Change", I would like to pass "Enabled" and "Disabled" as variables instead of static elements in the formula.

Rationale: my users may want to select "Activated" and "Disactivated" (or anything else) instead; and it would be better if they change that in a worksheet than in the VBA, with the variables (and their content) from the worksheet being passed to the VBA formula.

Any idea on how to do that with "Private Sub Worksheet_Change"?

In advance, thank you.

 

[JBC007]

I managed to make it work with the gracious help of Debra Dalgleish from Contextures.com.
Here is how it works:

In the sheet "Module" the user clicks on the cell named Modules_Selection.
This cell contains a drop-down list with 2 items: "Enabled" or "Disabled".
These 2 items are coming from 2 other cells in another sheet, each cell being named: ModuleEnabled and ModuleDisabled.

When the user selects "Enabled", the following Private Sub located in the sheet "Module" will make a number of sheets visible.
When the user selects "Disabled", the following Private Sub located in the sheet "Module" will make a number of sheets hidden.

Here, the names of the sheets are transferred as string from other cells where they are stored.
Actually, these other cells pick-up, by formula, the current name of the tab as seen by the user.

This makes the Sub very dynamic and quite resilient.
Anyway, it works like a charm!

Private Sub Worksheet_Change(ByVal Target As Range)

    'Initiate the variables
    Dim ValueEnabled As String
    Dim ValueDisabled As String
    Dim ValueSelected As String
    Dim SheetName1 As String
    Dim SheetName2 As String
    Dim SheetName3 As String

    
    '*** Set values that are valid for all modules ***
    '*** ----------------------------------------- ***
    ValueEnabled = ThisWorkbook.Names("ModuleEnabled").RefersToRange.Value
    ValueDisabled = ThisWorkbook.Names("ModuleDisabled").RefersToRange.Value

    '*** Module 1 ***
    '*** -------- ***
    
    ' Set values that are valid for this module only
    ValueSelected = ThisWorkbook.Names("Modules_Selection").RefersToRange.Value
    SheetName1 = ThisWorkbook.Names("Modules_Sheet_Test1").RefersToRange.Value
    SheetName2 = ThisWorkbook.Names("Modules_Sheet_Test2").RefersToRange.Value
    SheetName3 = ThisWorkbook.Names("Modules_Sheet_Test3").RefersToRange.Value
    
    ' If the user selects something in the drop-down list in the cell named as followed...
    If Target.Address = Me.Range("Modules_Selection").Address Then
        
        ' If the user selects either "ValueEnabled" or "ValueDisabled" in the drop-down list of that cell...
        If Target.Value = ValueEnabled Or Target.Value = ValueDisabled Then
        
            ' If the user selects "ValueEnabled" in the drop-down list...
            If Target.Value = ValueEnabled Then
                
                ' Make the following worksheets "Visible"
                Sheets(SheetName1).Visible = xlSheetVisible
                Sheets(SheetName2).Visible = xlSheetVisible
                Sheets(SheetName3).Visible = xlSheetVisible
                
            ' If the user selects "ValueDisabled" in the drop-down list...
            Else
            
                ' Make the following worksheets "VeryHidden"
                Sheets(SheetName1).Visible = xlSheetVeryHidden
                Sheets(SheetName2).Visible = xlSheetVeryHidden
                Sheets(SheetName3).Visible = xlSheetVeryHidden
           
            End If
        End If
    End If

End Sub

 

[Fazza]

Hi

You may be interested in this, though maybe not - expanding on Rosen's comments about a UDF can not do what you want.

From Chip Pearson's wonderful site, Writing Your Own Functions In VBA

And to quote from there,

A UDF can only return a value to the cell(s) whence it was called -- it must not modify the contents or formatting of any cell and must not modify the operating environment of Excel. If you attempt to change anything, the function will terminate immediately and return a #VALUE error to the calling cell.

And another link, The Spreadsheet Page - Excel Oddities: A User-Define Function Can’t Change The Worksheet. Oh Yeah?


regards

 

[JBC007]

Thanks, Fazza.