buttons and multi sheets

[parsec]

Hi to all
I have this code courtesy of NimRod

Sub ReplaceIt()
On Error GoTo Xit
With Cells
Set C = .Find("-", LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
Do
C.NumberFormat = "@"
C.Value = Replace(C.Value, "-", "")
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With
Xit:
End Sub

I have set in module and I can run it from the open sheet as a macro to be applied to any sheet that I have open.
What I would like to know is how I can set it up to run from a button that I have in an sheet that is not build from the VB.

Thank you
John

 

[SIXTH SENSE]

hi!

try this one. make sure that the name of the button is commandbutton1

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    <SPAN style="color:#00007F">Call</SPAN> ReplaceIt
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[parsec]

Thank you
That work but now I am still facing with the issue when the form is open I can't open another sheet and when I close the form it take me back to the VB area. Any info how to have the form open and still be able to open other sheets and apply the button(s) commands from that sheet?

Thank you
Jonh

 

[SIXTH SENSE]

hi!
in dsign mode of the form you turn the showmodal to false in
the property editor of the form.

 

[parsec]

Hi SIXTH SENSE
thank you, if you were close I will had treet you to a good beer or what ever you drink?

Take care
Jonh

 

[SIXTH SENSE]

hahahaha!
beer makes me sick!
I still have the hangover from our town festival here last sunday!

 

[parsec]

But is nice that you can still give advise after all that.

Be well and prosperous

 

[SIXTH SENSE]

I just enjoying this job!
As most of the people here, who just smile and feel contented
with a "THANK YOU"! Besides, the more i got some questions answered, the more i learn Xcel. We all jus keep on Xceling here!

 

[parsec]

I feel the same way it has been a great knowledge well for me,

May I ask one more ? how I can have the form open when I open that sheet, so I don't have to open it from the code editor?

Thank's

 

[SIXTH SENSE]

ok!
if you like it to pop frm a button, then on the
button click event add this code.
userform1.show
Besusre to unload it upon exit to minimize memory usage.
unload userform1