Change syntax

  • Thread starter Thread starter Ben in CA
  • Start date Start date
B

Ben in CA

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
 
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.)
 
Check your later post.
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.)
Click to expand...
 
Back
Top