Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.
Adding a new way to input Data into Excel
EE Pros...... HAPPY NEW YEAR!
I am looking for a easy way for users to input some simple information. I've attached a sample of what I'm looking for. Basically, it's taking perhaps a Form (open to any approach) and have the ability to input two variables (Amount per month and Number of Months) and have the product placed in a particular cell. The Form or input capability must be triggered by some action.... I was thinking about a triple click on the specific cell for the Form to come up and then a click on the Form to extract it. If the User doesn't want to use the Form (i.e. they put the amount in themselves, they can put it in without firing the Form up.
Got it?
Questions, ideas or a solution is welcome!
Thank you in advance,
B.
Adding-a-input-capability.xlsm
I am looking for a easy way for users to input some simple information. I've attached a sample of what I'm looking for. Basically, it's taking perhaps a Form (open to any approach) and have the ability to input two variables (Amount per month and Number of Months) and have the product placed in a particular cell. The Form or input capability must be triggered by some action.... I was thinking about a triple click on the specific cell for the Form to come up and then a click on the Form to extract it. If the User doesn't want to use the Form (i.e. they put the amount in themselves, they can put it in without firing the Form up.
Got it?
Questions, ideas or a solution is welcome!
Thank you in advance,
B.
Adding-a-input-capability.xlsm
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Bright01, Happy New Year!
Do you want something like this? Please see attached. Click on the macro button fill the number of months and value per month and you will get the desired output in Cell f7.
Adding-a-input-capability.xlsm
Do you want something like this? Please see attached. Click on the macro button fill the number of months and value per month and you will get the desired output in Cell f7.
Adding-a-input-capability.xlsm
Or these two
Whenever cell F7 is selected the Macro will automatically runs. or Attachment (V3) Whenever cell F7 is selected the a msgbox appears asking whether you want to run the macro or not.
See attached examples. Select Cell F7 and follow the macro.
Adding-a-input-capability--v2-.xlsm
Adding-a-input-capability--v3-.xlsm
Whenever cell F7 is selected the Macro will automatically runs. or Attachment (V3) Whenever cell F7 is selected the a msgbox appears asking whether you want to run the macro or not.
See attached examples. Select Cell F7 and follow the macro.
Adding-a-input-capability--v2-.xlsm
Adding-a-input-capability--v3-.xlsm
Not sure if I understood what you wanted, but give this a try.
If you just click into the two cells you mentioned, you can type a value. If you double click into the cells, then an input form will pop up and values can be entered, and the result placed in the cell when OKAY is pressed.
I didn't go crazy with formatting or error checking, but this should give you some ideas.
~bp
EE28913525.xlsm
If you just click into the two cells you mentioned, you can type a value. If you double click into the cells, then an input form will pop up and values can be entered, and the result placed in the cell when OKAY is pressed.
I didn't go crazy with formatting or error checking, but this should give you some ideas.
~bp
EE28913525.xlsm
Bill, will take a look. Excel amusant, #2 is close. You should be able to enter your own number in the cell (Cell F7) OR, by either clicking or hovering, have the question form come up. Can you combine the input data into the form so that you can put both $ / Mo. And # / Mo. In the same box? Then the amount is placed in the same box....F7. And after the data is entered the box can close. The example below was to show what it looked like once the data was entered.
Adding-a-input-capability--v2-.xlsm
Adding-a-input-capability--v2-.xlsm
Bill, closest yet! That's what I'm looking for. Doubleclick on the cell and up comes the box to enter the simple data. The math then puts the product back into the box. Can you set it up so that I can do multiple cells? + error checking? Is it contained in the WS or a module? Better in the specific worksheet since I have my production version in multiple WSs and wouldn't need this capability anywhere else.
It does currently support multiple cells, take a look at the VBA code associated with Sheet1 (also included below). Notice the check for which cells to enable this on.
There is some basic editing for numeric values, what additional editing would make sense. That can be added to the btnOkay_Click event in the VBA code associated with the button in the frmData form (also included below).
That is all the code that was needed, and the form itself. The form and it's VBA resides in the Workbook, while the triggering logic lives in the VBA associated with Sheet1.
Sheet1 VBA code
There is some basic editing for numeric values, what additional editing would make sense. That can be added to the btnOkay_Click event in the VBA code associated with the button in the frmData form (also included below).
That is all the code that was needed, and the form itself. The form and it's VBA resides in the Workbook, while the triggering logic lives in the VBA associated with Sheet1.
Sheet1 VBA code
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$F$7" Or Target.Address = "$F$20" Then
frmData.Show
End If
End Sub
Private Sub btnOkay_Click()
With Me
If .txtDollars <> vbNullString And .txtNumber <> vbNullString Then
ActiveCell.Value = .txtDollars.Value * .txtNumber
End If
.Hide
End With
End Sub
Private Sub txtDollars_Change()
NumberEdit
End Sub
Private Sub txtNumber_Change()
NumberEdit
End Sub
Private Sub NumberEdit()
If TypeName(Me.ActiveControl) = "TextBox" Then
With Me.ActiveControl
If Not IsNumeric(.Value) And .Value <> vbNullString Then
MsgBox "Only numbers allowed"
.Value = vbNullString
End If
End With
End If
End Sub
Please see attached.
Now anywhere you double click the macro will run. If you want only to enable double click in Cell F7 & F20 then replace the existing module with the following code in worksheet event.
Now anywhere you double click the macro will run. If you want only to enable double click in Cell F7 & F20 then replace the existing module with the following code in worksheet event.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$F$7" Or Target.Address = "$F$20" Then
Call test
End If
End Sub
Looking at both of these great examples......
Bill, if I wanted to add say, 5 cells that have this capability, how would I change this part of the code (such as what you have put here for F7 and F20)? In other words, what if I have a list of these cells?
Private Sub Worksheet_BeforeDoubleClic
If Target.Address = "$F$7" Or Target.Address = "$F$20" Then
DataEntrySaaS.Show
End If
BTW; I changed the name of the Form.
B.
Bill, if I wanted to add say, 5 cells that have this capability, how would I change this part of the code (such as what you have put here for F7 and F20)? In other words, what if I have a list of these cells?
Private Sub Worksheet_BeforeDoubleClic
If Target.Address = "$F$7" Or Target.Address = "$F$20" Then
DataEntrySaaS.Show
End If
BTW; I changed the name of the Form.
B.
How about this approach:
Select Case Target.Address
Case "$A$1", "$F$7", "$F$20"
frmData.Show
End Select
Another approach would be to place all the cells you want this to occur on into a named range, and then test for that in the double click event. Here's an example of that, testing against the named range "Popup".
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not (Application.Intersect(Target, Range("Popup")) Is Nothing) Then
frmData.Show
End If
End Sub
Premium Content
You need an Expert Office subscription to comment.Start Free Trial