Thank you, thank you, thank you!! This is EXACTLY what I needed. Have a
great day.
"Dave Peterson" wrote:
> If I have a choice, I'll use the controls from the Forms toolbar. They seem to
> "weigh down" excel a lot less. You can't do as many things with them, but
> that's ok with me.
>
> This first routine adds a bunch of buttons to a range and assigns the same macro
> to each of those buttons.
>
> The second routine just gives you an idea how to know what button was
> clicked--and it clears a cell a long way away on the same sheet (so use a test
> worksheet!).
>
> Option Explicit
> Sub FillARangeWithButtons()
> Dim myRng As Range
> Dim myCell As Range
> Dim myBTN As Button
>
> With ActiveSheet
> Set myRng = .Range("b2:b10")
> .Buttons.Delete 'nice for testing
> End With
>
> For Each myCell In myRng.Cells
> With myCell
> Set myBTN = .Parent.Buttons.Add(Top:=.Top, Left:=.Left, _
> Width:=.Width, Height:=.Height)
> End With
>
> With myBTN
> .Caption = "BTN_" & .TopLeftCell.Address(0, 0)
> .OnAction = "'" & ThisWorkbook.Name & "'!" & "myBTNMac"
> End With
> Next myCell
> End Sub
> Sub myBTNMac()
> Dim myBTN As Button
>
> Set myBTN = ActiveSheet.Buttons(Application.Caller)
>
> With myBTN
> MsgBox .Caption & vbLf & .TopLeftCell.Address & vbLf & .Name
> .TopLeftCell.Offset(0, 12).ClearContents
> End With
> End Sub
>
> Both of these routines would be placed in a General module--not behind the
> worksheet.
>
> BHatMJ wrote:
> >
> > Thank you! It's the first solid indication I've had that this can actually
> > be done. I was beginning to think that it wasn't possible.
> >
> > That said, the code line to insert text into the code module causes an Excel
> > error that closes Excel down every time. I can try to track down why but am
> > sure it would take me forever. Can you explain or give me an example of code
> > that would use buttons from the Forms toolbar?
> >
> > Thank you very much for all of your help!!!
> >
> >
> > > Then you can use the same macro (in a general module) for each button. Your
> > > code would have to figure out what button called it, though (depending on what
> > > your subroutine did).
> > >
> >
> > "Dave Peterson" wrote:
> >
> > > Here's some code that does one commandbutton:
> > >
> > > Option Explicit
> > > Sub testme()
> > >
> > > Dim OLEObj As OLEObject
> > > Dim wks As Worksheet
> > >
> > > Set wks = ActiveSheet
> > >
> > > With wks
> > > With .Range("a1:b2")
> > > Set OLEObj = .Parent.OLEObjects.Add _
> > > (ClassType:="Forms.CommandButton.1", _
> > > Link:=False, DisplayAsIcon:=False, _
> > > Left:=.Left, Top:=.Top, _
> > > Width:=.Width, Height:=.Height)
> > > End With
> > >
> > > With .Parent.VBProject.VBComponents(.CodeName).CodeModule
> > > .InsertLines .CreateEventProc("Click", OLEObj.Name) + 1, _
> > > "Msgbox ""Hi"""
> > > End With
> > > End With
> > >
> > > End Sub
> > >
> > > =====
> > > I'm not sure how married you are to the idea of using commandbuttons from the
> > > control toolbox toolbar, but you may want to consider using buttons from the
> > > Forms toolbar.
> > >
> > > Then you can use the same macro (in a general module) for each button. Your
> > > code would have to figure out what button called it, though (depending on what
> > > your subroutine did).
> > >
> > >
> > >
> > >
> > >
> > > BHatMJ wrote:
> > > >
> > > > HELP! I am adding a dynamic number of control buttons to a worksheet
> > > > dependent upon user input during run time (see my code below). I need to add
> > > > code to each of these buttons (also during run time) so that a subroutine is
> > > > called when the user selects the button.
> > > >
> > > > ANY help would be greatly appreciated!!!
> > > >
> > > > Sub AddCtrl(nCnt As Integer)
> > > >
> > > > topPos = 20
> > > > ' nCnt is an integer set at run time by the user
> > > > For lp = 1 to nCnt
> > > > ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=100,
> > > > Top:=topPos, Width:=50, Height:= 30).Select
> > > > topPos = topPos + 75
> > > > Next lp
> > > >
> > > > End Sub
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>
|
Similar Threads
