Hi,

I'd like a macro that does the following logic when I press a command button
- how do I format this "code" for Excel's Visual Basic?

onclick {
if cell W6 of this worksheet is not empty or zero, copy it to field W6 on
worksheet "Sales" - unless that field is already full, then prompt to replace
or leave existing value.
}

Also, how do I clear a particular field in a different worksheet using a
macro command button?

Any responses appreciated!

Hi

Insert two buttons from the "Command Toolbox" menu, then rightclick on one
of them. Click View code, and insert the code below to the codesheet which
appears. Close the code sheet and exit design mode.

Private Sub CommandButton1_Click()
If Range("W6").Value < "" And Range("W6").Value < 0 Then
If Sheets("Sales").Range("W6").Value < "" Then
answer = MsgBox("Do you want to replace existing value?", vbYesNo)
If answer = vbYes Then Sheets("Sales").Range("W6") =
Me.Range("W6").Value
Else
Sheets("Sales").Range("W6") = Me.Range("W6").Value
End If
End If
End Sub

Private Sub CommandButton2_Click()
Sheets("Sales").Range("A1").ClearContents
End Sub

Hopes it helps

---
Per
"Ben in CA" skrev i meddelelsen
...
Hi,

I'd like a macro that does the following logic when I press a command
button
- how do I format this "code" for Excel's Visual Basic?

onclick {
if cell W6 of this worksheet is not empty or zero, copy it to field W6 on
worksheet "Sales" - unless that field is already full, then prompt to
replace
or leave existing value.
}

Also, how do I clear a particular field in a different worksheet using a
macro command button?

Any responses appreciated!

Hi Per,

Incredible - thanks so much!

Thanks for the super quick and super useful reply!

Ben

"Per Jessen" wrote:

Hi

Insert two buttons from the "Command Toolbox" menu, then rightclick on one
of them. Click View code, and insert the code below to the codesheet which
appears. Close the code sheet and exit design mode.

Private Sub CommandButton1_Click()
If Range("W6").Value < "" And Range("W6").Value < 0 Then
If Sheets("Sales").Range("W6").Value < "" Then
answer = MsgBox("Do you want to replace existing value?", vbYesNo)
If answer = vbYes Then Sheets("Sales").Range("W6") =
Me.Range("W6").Value
Else
Sheets("Sales").Range("W6") = Me.Range("W6").Value
End If
End If
End Sub

Private Sub CommandButton2_Click()
Sheets("Sales").Range("A1").ClearContents
End Sub

Hopes it helps

---
Per
"Ben in CA" skrev i meddelelsen
...
Hi,

I'd like a macro that does the following logic when I press a command
button
- how do I format this "code" for Excel's Visual Basic?

onclick {
if cell W6 of this worksheet is not empty or zero, copy it to field W6 on
worksheet "Sales" - unless that field is already full, then prompt to
replace
or leave existing value.
}

Also, how do I clear a particular field in a different worksheet using a
macro command button?

Any responses appreciated!

Hi Ben

Thanks for your reply. I'm glad to help.

Regards,
Per

"Ben in CA" skrev i meddelelsen
...
Hi Per,

Incredible - thanks so much!

Thanks for the super quick and super useful reply!

Ben

Hi Per,

Do you know how I would modify this so it could be done 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)?

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 Trade
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


Thanks Per! - you've been a great help!

(please take no offense - in case you aren't notified of replies, I'll
re-post this as a new topic.)

"Per Jessen" wrote:

Hi Ben

Thanks for your reply. I'm glad to help.

Regards,
Per

"Ben in CA" skrev i meddelelsen
...
Hi Per,

Incredible - thanks so much!

Thanks for the super quick and super useful reply!

Ben

Check your later post.

Ben in CA wrote:

Hi Per,

Do you know how I would modify this so it could be done 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)?

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 Trade
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

Thanks Per! - you've been a great help!

(please take no offense - in case you aren't notified of replies, I'll
re-post this as a new topic.)

"Per Jessen" wrote:

Hi Ben

Thanks for your reply. I'm glad to help.

Regards,
Per

"Ben in CA" skrev i meddelelsen
...
Hi Per,

Incredible - thanks so much!

Thanks for the super quick and super useful reply!

Ben




--

Dave Peterson