Toggle Button from Worksheet to Toolbar? 1

[shelby55]

Hello

I'm using Excel 2010. I currently have a button on each worksheet of my workbook which runs the following code to hide/show columns. Is there any way that I can take this code and add it as a toggle on the toolbar so whatever worksheet the user is in it will show/hide as applicable?

Private Sub ToggleButton1_Click()
If ToggleButton1.Value = False Then
Me.Unprotect
Range("B1:J1").EntireColumn.Hidden = False
ToggleButton1.Caption = "Hide Original"
Me.Protect
Else
Me.Unprotect
Range("B1:J1").EntireColumn.Hidden = True
ToggleButton1.Caption = "Show Original"
Me.Protect

End If

End Sub

Thanks very much.

 

[SkipVought]

hi,

I'd eliminate the button and strictly use a sheet event to run your procedure -- click on ONE heading cell (ANY one) and have the code run to toggle the column display.

Paste this code into the ThisWorkbook code window if you have no Workbook_SheetSelectionChange already coded...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim rng As Range
    
    If Target.Count > 1 Then Exit Sub   '[b]if multiple cells selected, get out![/b]
    
    Select Case Sh.Name
    '[b]if you have sheets that you do NOT want these columns to toggle, then list them here[/b]
        Case "Master", "Some Other Different Sheet Name"
        Case Else
    '[b]all other sheets will get the toggle treatment[/b]
            If Not Intersect(Target, Sh.Rows(1), Sh.UsedRange) Is Nothing Then
            
                Set rng = Sh.Range("B1:J1").EntireColumn
                
                Sh.Unprotect
                
                If rng.Hidden Then
                    rng.Hidden = False
                Else
                    rng.Hidden = True
                End If
                
                Sh.Protect
                
                Set rng = Nothing
            End If
    End Select
    
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Thanks Skip! I'll give it a whirl and let you know how I do!

 

[shelby55]

Hi Skip

You are awesome - this works great!! Thank you so much!

What function would I use to ensure that once a worksheet was activiated it went to a certain cell in the worksheet?

 

[SkipVought]

Is it the same cell for each sheet? This would select A1.

Sh.cells(1,"a").select

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Thanks Skip...I assumed I would put this after the End Select but when I go into each worksheet it doesn't take me to A1. Your assumption is correct that it is the same cell on each worksheet.

 

[shelby55]

I lied, it works....I love this feature! What other kinds of things would you use this function for, Skip?

 

[shelby55]

Hey Skip

Sorry to be a pain but I realized why this wouldn't be practical and that is because users will be entering information so if everytime they are on a line and click it, they are taken to cell A1.

I just want it where upon activating the worksheet it takes them to A1. Is that possible? Thanks.

 

[SkipVought]

    'all other sheets will get the toggle treatment
            If Not Intersect(Target, Sh.Rows(1), Sh.UsedRange) Is Nothing Then[b]
                If Target.Column = 1 Then exit sub[/b]
                Set rng = Sh.Range("B1:J1").EntireColumn

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi Skip

Sorry but I'm not understanding what this code is doing?

 

[SkipVought]

its exiting the procedure when the selected column is 1, AFTER determining that the selection was in the table headers.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Sorry Skip that's not what I want. Unrelated to the hide/show columns, I just want that whenever the user enters the worksheet, they are taken to cell A1. Sort of like a "lost focus" event in Access. Maybe I need the "worksheet_activate" event? Thanks.

 

[SkipVought]

But it IS related to hide/show because A1 is IN THE HEADERS!

So if you want A1 to be the rest cell for each sheet, then I'd guess, from your post of 3 Oct 12 21:44, that you did not want selecting A1 to toggle the hide/show.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi Skip

The header row for hide/show that is working well is K to R. The cell that I wish to be the go to cell upon activating the worksheet is K5 (I was using A1 as generic but K5 is the actual cell I want).

Skip, I'm just not sure what the code you are providing is doing. Because of the way the macro runs to copy the template to each worksheet, it ends at cell E119 but I want the user to be at K5 when they activate each worksheet. Even if I added the code to the copy macro, the user could still enter data and not leave it set with the cursor at K5...so how do I do that?

Thanks.

 

[SkipVought]

This issue then has nothing at all to do with the issue addressed in this thread?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

That is correct.

 

[SkipVought]

Then disregard the last code snippet that had target.column.

What function would I use to ensure that once a worksheet was activiated it went to a certain cell in the worksheet?

On ANY & EVERY sheet that is Activated and is it the same cell on each sheet?

If not, which sheets to include or exclude?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Sheets to exclude are the same to exclude for the hide columns:
Case "Master", "Reabstracters", "Master_NewB", "Mapping_NewC", "Master_NewC", _
"RawData_A", "Mapping_NewA", "RawDataA_Map", "Values", "Master_NewA", "Readme_Clients", _
"Mapping_NewB", "Rat_Val", "Features

 

[SkipVought]

Use the Workbook_SheetActivate event and your Select Case.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Thanks Skip, I was able to do this.

The final code is:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
    'if you have sheets that you do NOT want these columns to toggle, then list them here
        Case "Master", "Reabstracters", "Master_NewB", "Mapping_NewC", "Master_NewC", _
        "RawData_A", "Mapping_NewA", "RawDataA_Map", "Values", "Master_NewA", "Readme_Clients", _
        "Mapping_NewB", "Rat_Val", "Features"
        
        Case Else
    'all other sheets will get the toggle treatment
        Sh.Unprotect
            Sh.Range("K5").Select
        Sh.Unprotect
End Select

End Sub