Copy command buttons or fixed worksheet header

[blackduck]

Is there a way to copy a set of command buttons inclucing their names, properties, and onclick code to other existing worksheets? There are 4 command buttons, 2 dropdown lists - on about 20 worksheets.

Actually, they are just navigation buttons horizontally placed and the whole top quarter of every sheet is the same. Is there a way to just have one version of the top header and the body shows the selected worksheet (a bit like frames and target area of html)?

(this probably wont be the last time I have to change the buttons)

Any suggestions greatly appreciated
thanks
K

 

[firefytr]

No, there is no global "frame" of sorts. You may want to rethink your strategy here, as updating 20 sheets worth of controls will get old really fast. Would one just do? Could you have a control/summary sheet? I don't know your data structure or layout, but it's something you should probably re-evaluate.

-----------
Regards,
Zack Barresse

 

[CautionMP]

blackduck,
Here is a typed and roughly tested routine that will duplicate the controls on one sheet to all the other sheets in a workbook.

NOTE: This will not delete any exisiting controls contained in the destination sheet.

Sub ReplicateControls()
Dim wkbCurrent As Workbook
Dim wksSource As Worksheet, wksDestination As Worksheet
Dim shpToReplicate As Shape, shpNew As Shape

Set wkbCurrent = ActiveWorkbook
'This is the worksheet that has the controls to be copied
Set wksSource = wkbCurrent.Worksheets("Sheet1")
For Each wksDestination In wkbCurrent.Worksheets
  If wksDestination.Name <> wksSource.Name Then
    For Each shpToReplicate In wksSource.Shapes
      Set shpNew = wksDestination.Shapes.AddFormControl(shpToReplicate.FormControlType, _
                   shpToReplicate.Left, shpToReplicate.Top, _
                   shpToReplicate.Width, shpToReplicate.Height)
      With shpNew
        .Name = shpToReplicate.Name
        .OnAction = shpToReplicate.OnAction
        'Control specific properties
        If .FormControlType = xlButtonControl Then
          .DrawingObject.Caption = shpToReplicate.DrawingObject.Caption
        ElseIf .FormControlType = xlDropDown Then
          .ControlFormat.ListFillRange = shpToReplicate.ControlFormat.ListFillRange
        End If
      End With
    Next shpToReplicate
  End If
Next wksDestination
Clean_Up:
Set shpNew = Nothing
Set shpToReplicate = Nothing
Set wksDestination = Nothing
Set wksSource = Nothing
Set wkbCurrent = Nothing
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[adalger]

Anything wrong with putting the buttons in a subform?

 

[rmikesmith]

If the buttons perform the same functions from sheet to sheet, you might consider a custom toolbar.


Regards,
Mike

 

[blackduck]

Thanks for the suggestions - hadn't thought of subform or toolbar and will probably do this next time.

CMP - I have been trying to use your code but get 'run-time error '9'. Subscript out of range.'
Maybe something to do with I'm using ActiveX controls and not form controls.

Will keep trying - Im not great at vb.

thanks
k

 

[firefytr]

Are you running a Mac? OS version? Excel version?

-----------
Regards,
Zack Barresse

 

[CautionMP]

blackduck,
When you get the error, and choose debug, is the following line highlighted?

...
'This is the worksheet that has the controls to be copied
[highlight]Set wksSource = wkbCurrent.Worksheets("Sheet1")[/highlight]
For Each wksDestination In wkbCurrent.Worksheets
...

If so it's becasue you have no [tt]Sheet1[/tt]. Just replace the [tt]Sheet1[/tt] with the name of the worksheet you want to copy the controls from.

In the future a toolbar is probably the way to go.

CMP

_{adalger, can you do subforms in Excel or are you thinking Access?}

(GMT-07:00) Mountain Time (US & Canada)

 

[firefytr]

..In the future a toolbar is probably the way to go.

Agreed!

-----------
Regards,
Zack Barresse