Command Button

[NJD]

Is there a method of making a command butoon visible on all worksheets
without creating a new object on each sheet? I've put it in a userform, but
then I cannot navigate between sheets without closing the form.

 

[Gary''s Student]

Why not create the same object on each sheet??:

Sub Macro2()
For Each sh In Worksheets
sh.Activate
ActiveSheet.Buttons.Add(248.25, 75.75, 90.75, 51).Select
Selection.OnAction = "hello"
Range("A1").Select
Next
End Sub

The usual way is to put the button on a toolbar.

 

[FSt1]

hi
if you have to close the form to navigate sheet, your from is modal, meaning
that the form has focus untll closed. you need to make the form modaless. how.
you probable have a sub that shows the form, maybe something like this.
Sub mac1ShoCal()
Load frmCalendar
frmCalendar.Show
end sub

modal = t and is default
modalless = false
to make the form modaless place a zero(false) after the show command as in
the above example.....

frmCalendar.Show 0

look up the showmodal property in vb help.

Regards
FSt1

 

[NJD]

New sheets will be added to the file regularly which makes adding a button to
each worksheet as the sheets are added more complicated than most of the
folks using hte file can handle.. Settign the form modal to 0 will suffice.

 

[Chip Pearson]

You can't have the same button on more than one sheet. You could put it on a
UserForm and show the form modelessly. Then, the form will be visible but
you can navigate between sheets. The form will just float there, waiting to
be clicked.

UserForm1.Show vbModeless '<< must include vbModeless to the Show method


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)