CODEWITHSUNDEEP
vbaexcelbuttonfor-loopworksheet
Eli Greenberg
Eli Greenberg

Reputation: 321

Creating command button on Navigation worksheet for all other worksheets

Current code:

Sub CreateFormsButton()

Dim ws As Worksheet
Dim btn As Button
Dim rng As Range
Dim counter As String
counter = 6

For Each ws In ThisWorkbook.Worksheets
     If ws.Name = "Navigation" Or ws.Name = "Todo" Then
     Else
     With ws
        Set rng = ThisWorkbook.Sheets("Navigation").Range("C" + CStr(counter))
        Set btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height * 2)
     With btn
        .Caption = ws.Name
        .OnAction = "GoToWS"
        .Font.Bold = True
        .Font.Size = 16
     End With
     End With
     End If
     counter = counter + 2
Next ws

End Sub

The code sort of works as is, however, the buttons are being placed on the worksheets themselves instead of the Navigation worksheet as intended. Am I doing something incorrectly by setting Set rng = ThisWorkbook.Sheets("Navigation").Range("C" + CStr(counter))? This doesn't seem to be having any effect on where the button is being placed. Thanks for your help.

Upvotes: 0

Views: 120

Answers (1)

Vincent G
Vincent G

Reputation: 3188

You need to add the button in the Buttons collection of the Navigation Sheet.

Set btn = ThisWorkbook.Sheets("Navigation").Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height * 2)

Upvotes: 1

Related Questions