Hello,

I need a macro to add a button to a spreadsheet cell and automatically
assign a macro to it. I understand how to do it manually in Excel, but it's
not easy to adjust the button's rect to fit the cell.

Thanks

Here is one example you can look over...


Mark Ivey


Sub AddButtonAndMacroToSheet()

' Change this selection as needed
Range("D11").Select

' This will add a button to the activecell
' which was selected in the previous step
ActiveSheet.Buttons.Add(ActiveCell.Left, _
ActiveCell.Top, _
ActiveCell.Width, _
ActiveCell.Height).Select

' This will assign a macro to this button
Selection.OnAction = "Macro1"
End Sub

Hello,

I am writing to check the status of the issue on your side. Would you mind
letting me know the result of Mark's suggestions? Mark's solution works
well based on my tests. If you need further assistance, feel free to let me
know. I will be more than happy to be of assistance.

Have a great day!

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

Hi folks,

I'm pleased to see such a straightforward post about this topic...
However, when I implement this method I get an, "Argument not
optional" message box. I'll give you my code:

Sub AddButtonandMacroToSheet()
Range("I2").Select
ActiveSheet.Buttons.Add(ActiveCell.Left, _
ActiveCell.Top, _
ActiveCell.Width, _
ActiveCell.Height).Select

Selection.OnAction = "GetPPID"
End Sub

Here's the code for my macro:

Function GetPPID(PPID) As String
Const URL As String = _
"https://report.converge.com/dell/internal/check_battery.php?
ppid="
Const FRAG1 As String = "'green'"
Const FRAG2 As String = "</FONT"
Dim msxml As Object
Dim rV, tmp, pos1, pos2

rV = ""

If PPID < "" Then
Set msxml = CreateObject("Microsoft.XMLHTTP")
msxml.Open "Get", URL & PPID, False
msxml.send

tmp = msxml.responseText

pos1 = InStr(tmp, FRAG1)
pos2 = InStr(tmp, FRAG2)

' If pos1 0 And pos2 0 Then
' rV = Left(tmp, pos2 - 1)
' rV = Right(rV, Len(rV) - (pos1 + Len(FRAG2)))
' End If

rV = tmp ' to be parsed later

Set msxml = Nothing
End If

GetPPID = Right(Left(rV, 83), 58)

' example of return string to be parsed
' [center][BR][BR][BR][BR][FONT COLOR='green']
' PPID: THE-BARCODE-HE KEEP AT CONVERGE![/font][BR]
' [BR][HR][BR][BR][FORM ACTION = 'check_battery.php']PPID:
' [INPUT TYPE='text' NAME = 'ppid'][BR][BR][INPUT TYPE='SUBMIT'
' VALUE = ' SUBMIT '][/FORM]

End Function

Jaybird...

I don't think the Selection.OnAction can call a function...

I believe it has to be a Sub.

Mark



"Jaybird" wrote in message
...
Hi folks,

I'm pleased to see such a straightforward post about this topic...
However, when I implement this method I get an, "Argument not
optional" message box. I'll give you my code:

Sub AddButtonandMacroToSheet()
Range("I2").Select
ActiveSheet.Buttons.Add(ActiveCell.Left, _
ActiveCell.Top, _
ActiveCell.Width, _
ActiveCell.Height).Select

Selection.OnAction = "GetPPID"
End Sub

Here's the code for my macro:

Function GetPPID(PPID) As String
Const URL As String = _
"https://report.converge.com/dell/internal/check_battery.php?
ppid="
Const FRAG1 As String = "'green'"
Const FRAG2 As String = "</FONT"
Dim msxml As Object
Dim rV, tmp, pos1, pos2

rV = ""

If PPID < "" Then
Set msxml = CreateObject("Microsoft.XMLHTTP")
msxml.Open "Get", URL & PPID, False
msxml.send

tmp = msxml.responseText

pos1 = InStr(tmp, FRAG1)
pos2 = InStr(tmp, FRAG2)

' If pos1 0 And pos2 0 Then
' rV = Left(tmp, pos2 - 1)
' rV = Right(rV, Len(rV) - (pos1 + Len(FRAG2)))
' End If

rV = tmp ' to be parsed later

Set msxml = Nothing
End If

GetPPID = Right(Left(rV, 83), 58)

' example of return string to be parsed
' [center][BR][BR][BR][BR][FONT COLOR='green']
' PPID: THE-BARCODE-HE KEEP AT CONVERGE![/font][BR]
' [BR][HR][BR][BR][FORM ACTION = 'check_battery.php']PPID:
' [INPUT TYPE='text' NAME = 'ppid'][BR][BR][INPUT TYPE='SUBMIT'
' VALUE = ' SUBMIT '][/FORM]

End Function