Toggle Button copy same toggle instructions

[SteveM99]

I am trying to use the same toggle button on another part of the same sheet. My worksheet has many columns and its a pain for the user to scroll all the way back to the left or right depending on where I place the original/first toggle button. All I want to do is replicate the toggle button somewhere else on the same sheet that does the same exact thing. I believe someone has come across this issue before. Maybe a toggle button is not correct to use but I basically need it to do just 2 functions at the moment so toggle button makes sense, either function 1 or function 2. But I want to press a button either by column A or over by Column AQ that does the same toggle action.

Bonus round....Is there a way that the toggle button would float with the user scrolling like ads/banners do on web sites as you scroll? Probably a lot of code and I don't need this part but it would be too cool for the users.

[mdmackillop]

You can use both buttons to call the same code

Private Sub CommandButton1_Click()
Call Test
End Sub


Private Sub CommandButton1_Click()
Call Test
End Sub


Private Sub Test()
MsgBox ActiveSheet.Name
End Sub

This should keep the button in the top left corner

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With CommandButton1
        .Top = Cells(ActiveWindow.ScrollRow, 1).Top + 20
        .Left = Cells(1, ActiveWindow.ScrollColumn).Left + 20
    End With
End Sub

[SteveM99]

On the floating button any way to keep it at the top row of the sheet since the panes are frozen? I lose the button when I scroll down until I hit a cell. Would like it pinned in the first few rows before the pane freeze, say like row 1 and 2 (they are wide rows and can accommodate the button). Other wise, that is awesome functionality. Thank you for the code on that. Testing the same button code recommendation next which may make the floating button moot because you will always be able to see the buttons on opposite sides of the sheet.

SCRATCH THIS REPLY, FIGURED OUT TO JUST USE A VERY LARGER NEGATIVE NUMBER IN THE CODE TO KEEP THE BUTTON AT THE TOP IN MOST INSTANCES (-10000 INSTEAD OF -150 THAT I WAS USING).

[SteveM99]

I tried to call button but it did not work. I think the if true then else code is causing the issue. Below is code.

Private Sub ToggleButton3_Click()
'Sub PrintToEnd()
'Convert this over to an active X toggle to change print ranges between final color (V to AP) and working version (A to R)
'Worksheets("report1").Activate
If ToggleButton3.Value = True Then
ToggleButton3.Caption = "CLICK To Go To Working Mode"
Dim Z As Range, Zw As Long
With ActiveSheet
Zw = .Range("AQ:AQ").Find("RangeEnd").Row
Set Z = .Range("V1:AP" & Zw)
.PageSetup.PrintArea = Z.Address
With ActiveSheet.PageSetup
'.PrintTitleRows = ""
'.PrintTitleColumns = ""
.LeftMargin = Application.InchesToPoints(0.2)
.RightMargin = Application.InchesToPoints(0.2)
.TopMargin = Application.InchesToPoints(0.2)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PaperSize = xlPaperLegal
'.PaperSize = xlPaperA4
'.Orientation = xlPortrait 'xlLandscape
.Zoom = False
'.FitToPagesWide = 1
'.FitToPagesTall = 1
End With
ActiveWindow.DisplayGridlines = False
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True 'Print Preview
'ActiveWindow.SelectedSheets.PrintOut Copies:=1 'Printout
End With
'Add code to print if desired
'copied from toggle1 private macro
Dim rng As Range
Set rng = ActiveSheet.Range("A3:R307")
FilterField = WorksheetFunction.Match("Detail", rng.Rows(1), 0)
rng.AutoFilter Field:=FilterField, Criteria1:=Array( _
"1"), Operator:=xlFilterValues
'used to remove revenue top few rows
Rows("4:68").EntireRow.Hidden = True
'used to move cursor to final area
ActiveSheet.Range("ae1").Select
Else
ToggleButton3.Caption = "Click To Go To Final Mode"
Dim Y As Range, Yw As Long
With ActiveSheet
Yw = .Range("S:S").Find("RangeEnd").Row
Set Y = .Range("A1:R" & Yw)
.PageSetup.PrintArea = Y.Address
With ActiveSheet.PageSetup
'.PrintTitleRows = ""
'.PrintTitleColumns = ""
.LeftMargin = Application.InchesToPoints(0.2)
.RightMargin = Application.InchesToPoints(0.2)
.TopMargin = Application.InchesToPoints(0.2)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PaperSize = xlPaperLetter
'.PaperSize = xlPaperA4
'.Orientation = xlPortrait 'xlLandscape
.Zoom = False
'.FitToPagesWide = 1
'.FitToPagesTall = 1
End With
ActiveWindow.DisplayGridlines = False
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True 'Print Preview
'ActiveWindow.SelectedSheets.PrintOut Copies:=1 'Printout
End With
'copied from toggle1 private macro
Dim rngb As Range
Set rngb = ActiveSheet.Range("A3:R307")
FilterField = WorksheetFunction.Match("Detail", rngb.Rows(1), 0)
rngb.AutoFilter Field:=FilterField, Criteria1:=Array( _
"1"), Operator:=xlFilterValues
    
'copied from gotcell macro
ActiveSheet.Range("a1").Select
End If
End Sub

Private Sub ToggleButton4_Click()
Call ToggleButton3
End Sub

[mdmackillop]

I would put your "actions" into separate subs. Put code to your buttons initially to trigger message boxes to get the logic right; then just change the code to call the appropriate sub. Modular code makes it easier to pin down errors.

[mdmackillop]

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    With CommandButton1 
        .Top =  20 
        .Left = Cells(1, ActiveWindow.ScrollColumn).Left + 20 
    End With 
End Sub

[SteveM99]

I will work on breaking it down tomorrow and get back with you. Thank you so much on the further floating button coding below.