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" <(E-Mail Removed)> skrev i meddelelsen
news:37F6850D-8484-4258-8D5B-(E-Mail Removed)...
> 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" <(E-Mail Removed)> skrev i meddelelsen
> news:37F6850D-8484-4258-8D5B-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> skrev i meddelelsen
news:43652861-B389-487C-B4F7-(E-Mail Removed)...
> 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" <(E-Mail Removed)> skrev i meddelelsen
> news:43652861-B389-487C-B4F7-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> skrev i meddelelsen
> > news:43652861-B389-487C-B4F7-(E-Mail Removed)...
> > > Hi Per,
> > >
> > > Incredible - thanks so much!
> > >
> > > Thanks for the super quick and super useful reply!
> > >
> > > Ben
> > >
> >
> >

--

Dave Peterson