This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.
Small "twik" to a great Macro
EE Pros,
I just had two outstanding EE Pros help me out with a particular Macro. I just need two twiks. The first is when you click on a yellow cell, you will see a data entry form come up. How do I get the $ format in the dollar entry point so that when you put a number in, it shows as a currency? And the second twik is how do I get it to make the calculation real-time instead of using the "go" button? When both cells are filled it could post the answer then when you moved from the cell, it could clear and hide the form until the next cell is clicked.
Here is the sample.
Thank you in advance.
B.
DataEntry-with-Form-for-many-Cells.xlsm
I just had two outstanding EE Pros help me out with a particular Macro. I just need two twiks. The first is when you click on a yellow cell, you will see a data entry form come up. How do I get the $ format in the dollar entry point so that when you put a number in, it shows as a currency? And the second twik is how do I get it to make the calculation real-time instead of using the "go" button? When both cells are filled it could post the answer then when you moved from the cell, it could clear and hide the form until the next cell is clicked.
Here is the sample.
Thank you in advance.
B.
DataEntry-with-Form-for-many-Cells.xlsm
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Does this work for you?
Private Sub btnOkay_Click()
With Me
If .txtDollars <> "" Then
ActiveCell.Value = .txtDollars.Value
ActiveCell.Offset(1, 0).Select
End If
.Hide
End With
End Sub
Martin,
Not sure where your code goes here.
Here is what I have that works now:
Private Sub Worksheet_BeforeDoubleClic
Select Case Target.Address
Case "$A$1", "$F$7", "$F$20"
DataEntrySaaS.Show
End Select
End Sub
Here is what you sent me:
Private Sub btnOkay_Click()
With Me
If .txtDollars <> "" Then
ActiveCell.Value = .txtDollars.Value
ActiveCell.Offset(1, 0).Select
End If
.Hide
End With
End Sub
The $ I'm trying to get the macro to recognize is the Currency field (first one) in the Form itself. So when I put a number in the form, it shows as a $ amount. The currency in the yellow box is already done with the formatting of the cell where then product is placed (e.g. A7).
The other Twik was to attempt to be able to skip the "GO" button in the form. So when you put both values in the form, it automatically put the product in A7 and then closed the box. If you double click the cell (A7 again), the form would come up with the $ value only (so as not to confuse the macro since if both cells in the form were filled it would just close.
Does that make sense?
B.
Not sure where your code goes here.
Here is what I have that works now:
Private Sub Worksheet_BeforeDoubleClic
Select Case Target.Address
Case "$A$1", "$F$7", "$F$20"
DataEntrySaaS.Show
End Select
End Sub
Here is what you sent me:
Private Sub btnOkay_Click()
With Me
If .txtDollars <> "" Then
ActiveCell.Value = .txtDollars.Value
ActiveCell.Offset(1, 0).Select
End If
.Hide
End With
End Sub
The $ I'm trying to get the macro to recognize is the Currency field (first one) in the Form itself. So when I put a number in the form, it shows as a $ amount. The currency in the yellow box is already done with the formatting of the cell where then product is placed (e.g. A7).
The other Twik was to attempt to be able to skip the "GO" button in the form. So when you put both values in the form, it automatically put the product in A7 and then closed the box. If you double click the cell (A7 again), the form would come up with the $ value only (so as not to confuse the macro since if both cells in the form were filled it would just close.
Does that make sense?
B.
I misunderstood the use of the two textboxes on the form. Starting over I double-clicked F7 and put 123 in the dollar field of the form and 5 in the # field and pressed go. When I then selected a different cell on the worksheet F7 became "$ 615". What do you want to have happen differently?
Please see attached.
The cell can get updated without or with clicking the "Go" button. If you do not need the Go button just delete it from the userform.
Also the $ sign is in the textbox "Txtdollars".
Once the number entered in txtNumber double click on the textbox and cell will be updated.
DataEntry-with-Form-for-many-Cells.xlsm
The cell can get updated without or with clicking the "Go" button. If you do not need the Go button just delete it from the userform.
Also the $ sign is in the textbox "Txtdollars".
Once the number entered in txtNumber double click on the textbox and cell will be updated.
DataEntry-with-Form-for-many-Cells.xlsm
Martin, I now see where you were pointing me for the change and it works to add the $ sign.
Excel amusant, got it, however when I delete the Go Button, or/and the Go Button macro, I have to press the X to close the box and then it updates. Is there a way to make this like "real time"? As you change the numbers it auto changes the product? Then by proceeding to another cell, it will close the box?
Thanks both of you for this help..... I know it seems minor.
B.
Excel amusant, got it, however when I delete the Go Button, or/and the Go Button macro, I have to press the X to close the box and then it updates. Is there a way to make this like "real time"? As you change the numbers it auto changes the product? Then by proceeding to another cell, it will close the box?
Thanks both of you for this help..... I know it seems minor.
B.
No you do not have to press the X button to close it. Once you double click on the textbox it will automatically closed.
It can not be in real time you have to double click, or we can set it on mouse move. but this is not recommended.
Please see attached. I have now removed the Go button.
DataEntry-with-Form-for-many-Cells.xlsm
It can not be in real time you have to double click, or we can set it on mouse move. but this is not recommended.
Please see attached. I have now removed the Go button.
DataEntry-with-Form-for-many-Cells.xlsm
Another solution is to create a third textbox "Results" Once the numbers entered in textbox1 and textbox2 then textbox 3 will updated automatically and thus the cell will be updated and userform automatically gets closed.
See attached example with textbox3 added.
DataEntry-with-Form-for-many-Cells.xlsm
See attached example with textbox3 added.
DataEntry-with-Form-for-many-Cells.xlsm
Premium Content
You need an Expert Office subscription to comment.Start Free Trial