Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need a Macro to drive a "Helper Cell"

Posted on 2016-07-29
9
Medium Priority
?
26 Views
Last Modified: 2016-08-01
EE Pros,

I need a rather simple macro.  I need a macro that fires when you press the button and it produces a "1" or a "2" in the helper cell.

Sample attached.

Thank you in advance.

B.
Macro-for-Helper-Cell.xlsm
0
Comment
Question by:Bright01
  • 6
  • 3
9 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41734752
Sub RoundedRectangle1_Click()
Range("G8") = Int((2) * Rnd + 1)
End Sub

Open in new window

0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41734754
The above will randomly show 1 or 2.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41734854
You should also add this to the sheet code.
Private Sub Worksheet_Activate()
Randomize
End Sub

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Bright01
ID: 41734903
Martin,

If I do not want random, but want press once I get 1, press again, I get 2.  Then I comment out the Private Sub..... right?

Also, if I want to make it a doubleclick..... is it double_click?

B.
0
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 41734939
This is for single clicks.
Sub RoundedRectangle1_Click()
Static Toggle As Boolean
If Toggle Then
    Range("G8") = 2
    Toggle = False
Else
    Range("G8") = 1
    Toggle = True
End If
End Sub

Open in new window

0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41734943
Please see my post above, and AFAIK there is no double-click for a shape. You'd need to use an ActiveX command button but that's usually not a good idea.
1
 

Author Closing Comment

by:Bright01
ID: 41735282
It's a beautiful thing!  Thank you!

B.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41735314
You're welcome and if you want to you can change it to this.
Sub RoundedRectangle1_Click()
Static Toggle As Boolean
If Toggle Then
    Range("G8") = 2
Else
    Range("G8") = 1
End If
Toggle = Not Toggle

End Sub

Open in new window

1
 

Author Comment

by:Bright01
ID: 41737354
Thank you Martin!  More then expected, as always!

B.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question