How to run code automatically every time a drop-down validation menu changes the case

[elgatnegre]

Given two workbooks named as follows:
"W01_Clients.xls"
"W02_Macros.xls"

I wrote a piece of code in a module of the workbook "W02_Macros" to make a sheet (named "S02_AuxBackOffice") which it is placed in the workbook "W01_Clients.xls" to be visible or invisible when runing the code from the first workbook ("W02_Macros").

The code first checks the value in the cell "D6" of the sheet in which the code is placed and from which the code is meant to be called ("Sheet1" of the workbook "W02_Macros") and works as follows:

(the following code is written in a module of the workbook "W02_Macros")

Sub ShowHideBackOfficeSheets()
With Sheets("Sheet1")
If Range("D6").Value = True Then

Workbooks("W01_Clients.xls").Worksheets("S02_AuxBackOffice").Visible = True
Else
Workbooks("W01_Clients.xls").Worksheets("S02_AuxBackOffice").Visible = False
End If
End With
End Sub

As you can see, the code assumes that if the value found in cell "D6" of the "Sheet1" is = "True", then the macro will make visible the sheet I want to see ("S02_AuxBackOffice") which is placed in the other workbook ("W01_Clients.xls"). Otherwise, if the value in the cell "D6" is not = "True" (e.g. "D6" = "False") then the macro will make the same sheet invisible.

This works as follows: I use a check button linked to the macro (so that runs de code every time I click on it in order to check or uncheck the button) and which besides is linked to the cell "D6" (giving a value = "True" to the cell when the button is checked and False otherwise). Therefore, every time the user checks or unchecks the button from the workbook "W02_Macros", there are two actions that will start automatically: first, the value of the cell "D6" of the "Sheet1" changes, and second, the macro "ShowHideBackOfficeSheets()" will run and so it will make the "S02_AuxBackOffice" visible or invisible in the workbook "W01_Clients.xls".

The method works correctly, but the thing is that now I want to change the way to call (activate) the macro in order to change the state of the sheet from visible to invisible and viceversa. I don't want to do it through a check button anymore, but I want to do it by using a drop-down validation menu, placed in the cell "D6", from which the user can select between two options: 'show sheet' and 'hide sheet' BUT knowing these words from the drop-down validation menu are actually going to be shown in the language of the user (english='show sheet', spanish='mostrar hoja', etc.).

The target I need to achieve is that by every change in the drop-down menu placed in the cell "D6" of the "Sheet1" in the workbook "W02_Macros" the code would automatically run in order to make the sheet of the workbook "W01_Clients.xls" (the sheet named "S02_AuxBackOffice") visible or invisible as desired by the user. Please note that I have defined and named a range in the "Sheet2" of the workbook "W02_Macros" in order to create the validation list in the "Sheet1"and which contains the names of the cases included in the validation list in the language chosen by the user (english='show sheet', spanish='mostrar hoja', etc.).

Anyone can help me out?

 

[ParamRay]

.
.

Try something like the following. The macro must be placed in the code module for "Sheet1" in "W02_Macros.xls", otherwise it will not work!

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngChangeCell   As Range
    Dim varShowValues   As Variant
    Dim blnWksVisible   As Boolean
    Dim bytCounter      As Byte
    Dim strWbkName      As String
    Dim strWksName      As String
    
    Set rngChangeCell = Me.Range("D6")  'The range containing the dv list.
    If Intersect(Target, rngChangeCell) Is Nothing Then Exit Sub
    
    varShowValues = Array( _
        "Show sheet", _
        "Voir feuille", _
        "Zeigen blatt", _
        "Mostrar hoja", _
        "Laat vel") 'English, french, german, spanish, dutch.
                    'All possible choices that make the
                    'sheet visible should be added.
        
    blnWksVisible = False
    For bytCounter = LBound(varShowValues) To UBound(varShowValues)
        If rngChangeCell.Value = varShowValues(bytCounter) Then
            blnWksVisible = True
        End If
    Next bytCounter
    
    strWbkName = "W01_Clients.xls"
    strWksName = "S02_AuxBackOffice"
    
    On Error Resume Next
    Application.Workbooks(strWbkName).Worksheets( _
        strWksName).Visible = blnWksVisible
    
    If Err.Number <> 0 Then
        MsgBox "Cannot find the workbook '" & strWbkName & _
            "' or the worksheet '" & strWksName & "'.", vbExclamation
    End If
    
    On Error GoTo 0
End Sub

 

[elgatnegre]

Hey, hey, hey! Thank you very much for your effort and great intuitive solution.


However, we have a little problem here. The thing is curious: the code you wrote works well BUT it does not work automatically when I change the case selection in the drop-down menu (lets say, when I change the case selection from "Show sheet" to "Hide sheet" when I'm using the validation list in english).


On the contrary, the code will just work when I drag-and-drop the cell referenced by the macro to other position (for example, if I take the validation menu in the cell "D6" and drop it in the cell "D7", or in any other cell in the sheet (sheet1)!!!


So, the macro is activated when the validation drop-down menu initially placed in the cell "D6" is re-placed to other cells of the sheet1, but will not be activated just by the action of selecting a different case in the validation list.


The funny thing here is that the macro works well every time I replace the position of the list (if the case selection in the drop-down menu had been previously changed to other case, of course) and it works correctly —in this way— even if the validation menu lays in other cell than the one the code (placed in the module of the sheet1) has as reference position for the list (in the code "D6" is where the list is suposed to be, but the code runs every time I replace the position of the drop-down list anyway). Curious!


Any new great idea about why of this unwanted behaviour? Do you have any solution? I hope so, because the code you provided me with is gorgeous!


Thanks once again in advance


Note
If I could I would send you a pair of simple .xls files I've created just for trying your solution so that you could test it yourself. Is it possible to share a file here?