I would like to create a button on the worksheet to work a macro

[Steved]

Hello from Steved

Please how would I go about creating a button on the worksheet to work the
below macro. Thankyou.

Sub FindPart()
Dim res As String, saddr As String
Dim RgToSearch As Range, RgFound As Range
Dim secondValue As String
Set RgToSearch = ActiveSheet.Range("C:C")

res = Application.InputBox("Type School Number ie 001,8.00", _
"Find School", , , , , , 2)
If res = "False" Then Exit Sub 'exit if Cancel is clicked
res = Trim(UCase(res))
If res = "" Then Exit Sub 'exit if no entry and OK is clicked
If InStr(1, res, ",", vbTextCompare) = 0 Then
MsgBox "Invalid entry"
Exit Sub
End If
v = Split(res, ",")
res = Trim(v(LBound(v)))
secondValue = Trim(v(UBound(v)))
Set RgFound = RgToSearch.Find(what:=res, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If RgFound Is Nothing Then
MsgBox "School " & res & " not found."
Exit Sub
Else
saddr = RgFound.Address
Do
If RgFound.Offset(0, 1).Text = secondValue Then
Application.Goto Reference:= _
RgFound.Offset(0, -1).Address(True, True, xlR1C1)
Exit Do
End If
Set RgFound = RgToSearch.FindNext(RgFound)
Loop While RgFound.Address <> saddr

End If

[Tom Ogilvy]

Put the complete subroutine in a general module (Insert=>Module).

go back to the worksheet and in Tools=>Customize, click the Forms toolbar on
the first tab, or on the menu, right click and select Forms

Click the button icon on the toolbar, then go to the sheet and click with
the mouse, holding the button down, drag down and to the right to get the
size of the button you want.

You should then get the assign macro dialog, select your macro.

or right click on the button and select assign macro, then select your
macro.

--
Regards,
Tom Ogilvy


"Steved" <Steved@discussions.microsoft.com> wrote in message
news:10C98D8F-7A6C-47AF-A50B-86DABC06EFC4@microsoft.com...
> Hello from Steved
>
> Please how would I go about creating a button on the worksheet to work the
> below macro. Thankyou.
>
> Sub FindPart()
> Dim res As String, saddr As String
> Dim RgToSearch As Range, RgFound As Range
> Dim secondValue As String
> Set RgToSearch = ActiveSheet.Range("C:C")
>
> res = Application.InputBox("Type School Number ie 001,8.00", _
> "Find School", , , , , , 2)
> If res = "False" Then Exit Sub 'exit if Cancel is clicked
> res = Trim(UCase(res))
> If res = "" Then Exit Sub 'exit if no entry and OK is clicked
> If InStr(1, res, ",", vbTextCompare) = 0 Then
> MsgBox "Invalid entry"
> Exit Sub
> End If
> v = Split(res, ",")
> res = Trim(v(LBound(v)))
> secondValue = Trim(v(UBound(v)))
> Set RgFound = RgToSearch.Find(what:=res, _
> LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
> If RgFound Is Nothing Then
> MsgBox "School " & res & " not found."
> Exit Sub
> Else
> saddr = RgFound.Address
> Do
> If RgFound.Offset(0, 1).Text = secondValue Then
> Application.Goto Reference:= _
> RgFound.Offset(0, -1).Address(True, True, xlR1C1)
> Exit Do
> End If
> Set RgFound = RgToSearch.FindNext(RgFound)
> Loop While RgFound.Address <> saddr
>
> End If

[Steved]

Thankyou.

"Tom Ogilvy" wrote:

> Put the complete subroutine in a general module (Insert=>Module).
>
> go back to the worksheet and in Tools=>Customize, click the Forms toolbar on
> the first tab, or on the menu, right click and select Forms
>
> Click the button icon on the toolbar, then go to the sheet and click with
> the mouse, holding the button down, drag down and to the right to get the
> size of the button you want.
>
> You should then get the assign macro dialog, select your macro.
>
> or right click on the button and select assign macro, then select your
> macro.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Steved" <Steved@discussions.microsoft.com> wrote in message
> news:10C98D8F-7A6C-47AF-A50B-86DABC06EFC4@microsoft.com...
> > Hello from Steved
> >
> > Please how would I go about creating a button on the worksheet to work the
> > below macro. Thankyou.
> >
> > Sub FindPart()
> > Dim res As String, saddr As String
> > Dim RgToSearch As Range, RgFound As Range
> > Dim secondValue As String
> > Set RgToSearch = ActiveSheet.Range("C:C")
> >
> > res = Application.InputBox("Type School Number ie 001,8.00", _
> > "Find School", , , , , , 2)
> > If res = "False" Then Exit Sub 'exit if Cancel is clicked
> > res = Trim(UCase(res))
> > If res = "" Then Exit Sub 'exit if no entry and OK is clicked
> > If InStr(1, res, ",", vbTextCompare) = 0 Then
> > MsgBox "Invalid entry"
> > Exit Sub
> > End If
> > v = Split(res, ",")
> > res = Trim(v(LBound(v)))
> > secondValue = Trim(v(UBound(v)))
> > Set RgFound = RgToSearch.Find(what:=res, _
> > LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
> > If RgFound Is Nothing Then
> > MsgBox "School " & res & " not found."
> > Exit Sub
> > Else
> > saddr = RgFound.Address
> > Do
> > If RgFound.Offset(0, 1).Text = secondValue Then
> > Application.Goto Reference:= _
> > RgFound.Offset(0, -1).Address(True, True, xlR1C1)
> > Exit Do
> > End If
> > Set RgFound = RgToSearch.FindNext(RgFound)
> > Loop While RgFound.Address <> saddr
> >
> > End If
>
>
>