How can i change this Worksheet_Change event to a sub for a commandbutton?

[MisterExcellent]

With this code you can show or hide the input messages on the basis of a dropdown(true/false).
I want to use this with a commandbutton and not a dropdown.
I look forward to your approaches.

'APJToolTips 
Private Sub Worksheet_Change(ByVal Target As Range)
 Dim rng As Range
 Dim cel As Range
 If Target.Address = Me.Range("ShowUserMsg").Address Then
 Set rng = Me.Cells.SpecialCells(xlCellTypeAllValidation)
 For Each cel In rng.Cells
 With cel.Validation
 If .InputMessage <> "" Then .ShowInput = Target.Value
 End With
 Next cel
 End If
 End Sub

 

[Marcelo Branco]

Hi,

Maybe this

Insert a CommandButton assign the macro below to it

Sub testButton()
    Dim rng As Range, cel As Range
    
    Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)
    For Each cel In rng.Cells
        With cel.Validation
            .ShowInput = Not .ShowInput
        End With
    Next cel
 
End Sub

HTH

M.

 

[MisterExcellent]

Hi,

Maybe this

Insert a CommandButton assign the macro below to it

Sub testButton()
    Dim rng As Range, cel As Range
    
    Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)
    For Each cel In rng.Cells
        With cel.Validation
            .ShowInput = Not .ShowInput
        End With
    Next cel
 
End Sub

HTH

M.

Obrigado Marcelo!

I have another question.
I'd be delighted if you could conform this code as follows:

How would you change this code to show/not show the inputmsg for the whole active sheet (or the whole workbook) and not just specialcells?

 

[Marcelo Branco]

Obrigado Marcelo!

I have another question.
I'd be delighted if you could conform this code as follows:

How would you change this code to show/not show the inputmsg for the whole active sheet (or the whole workbook) and not just specialcells?

You are welcome!
(de nada! )

I am not sure if i understood what you mean by "not just special cells", since you can have (or not) input messages only in cells where data-validation was applied. As far as i know...

M.

 

[MisterExcellent]

You are welcome!
(de nada! )

I am not sure if i understood what you mean by "not just special cells", since you can have (or not) input messages only in cells where data-validation was applied. As far as i know...

M.

Mais uma vez, agradeço

In this Code i have to name the ranges with the Input Messages (Special Cells).
But i don't want to select the cells to be named.

Is it possible to use the ActiveSheet and not just ActiveSheet.Cells.NamedRange?

Até a próxima Marcelo!
(Until next time!)

 

[MisterExcellent]

Has anyone else an idea?

 

[MisterExcellent]

Who else knows another possibility for hiding/showing InputMessages in an ActiveSheet without selecting the cells to be named?

 

[MisterExcellent]

Help me please

Any suggestions?

 

[mikerickson]

Re: Help me please

I'm not sure what you are after.

If you want a message to appear every time a user selects any cell on a worksheet (same message for every cell) a SelctionChange event would me easier to manage than Validation (=TRUE) on every cell.

 

[MisterExcellent]

Re: Help me please

I'm not sure what you are after.

If you want a message to appear every time a user selects any cell on a worksheet (same message for every cell) a SelctionChange event would me easier to manage than Validation (=TRUE) on every cell.

Hi Mike,
thank you very much.
How would you do this?