Change from a Command Button to a Form Button

[Ben in CA]

Hi,

Per Jessen helped me with some code, and he did a great job, but I guess I
didn't ask for what would be the best! (Sorry Per)

Do you know how I would modify this code so it could be added and performed
from a form button instead of a command button?

(It's really useful already, but now I'm thinking it should have been a form
button.)

Also, is there any way that it could automatically replace (not ask) if the
value of C33 is 0 (zero), as well as empty?

Private Sub CommandButton1_Click()

If Range("W6").Value <> "" And Range("W6").Value <> 0 Then
If Sheets("Quick Calculator").Range("C33").Value <> "" Then
answer = MsgBox("Do you want to replace existing value for Thing
1?", vbYesNo)
If answer = vbYes Then Sheets("Quick Calculator").Range("C33") =
Me.Range("W6").Value
Else
Sheets("Quick Calculator").Range("C33") = Me.Range("W6").Value
End If
End If

End Sub

Thank you for any suggestions!

Ben

 

[FSt1]

hi
my general rule is form button for forms, command buttons for the sheet.
is there a reason why you think your code needs to be in a form button? do
you now have a form?
the 2 button would do the same thing.

but to answer your question...
transfer the command button code to a standard module with a different name.
if the form button is on the sheet(???), the right click it and click asign
macro the macro dialog should popup. scroll to your macro.
if the form button is on a form, then the process is much the same as with a
command button on the sheet.

if the form button is not on a form, i recommend that you leave it in the
command button.
my thoughts.

regards
FSt1

 

[Dave Peterson]

You can move the code to a general module and use something like:

Option Explicit
Sub BtnClick()
Dim Answer As Long
With ActiveSheet
If .Range("W6").Value <> "" _
And .Range("W6").Value <> 0 Then
If Sheets("Quick Calculator").Range("C33").Value <> "" Then
Answer = MsgBox("Do you want to replace " _
& "existing value for Thing 1?", vbYesNo)
If Answer = vbYes Then
Sheets("Quick Calculator").Range("C33") = .Range("W6").Value
End If
Else
Sheets("Quick Calculator").Range("C33") = .Range("W6").Value
End If
End If
End With
End Sub

Notice the Me keyword has been replaced with Activesheet (using the With/end
with structure).

And you'll rightclick on the forms button and assign this macro to the button.

 

[Ben in CA]

Thanks Dave!

The "Me" keyword is the issue I was running against.

FSt1, you have some good points, but I prefer the form button since it's
more easily customized and moved around - you don't have to be in Design mode.

Thanks!

 

[Dave Peterson]

I find that the buttons from the Forms toolbar behave better when there are lots
in the worksheet.

And I can assign the same macro to each button, too.