Forum Discussion
Create a pop up box for user data input that auto formulates specific cells
For Excel 2010, I have two columns created for counting. One counts up to a number and the other column counts backwards from that number to 1. Ex. First column counts up to 50 and the second column counts backwards from 50 to 1
I want to create a pop up box when the spreadsheet is first opened where the user inputs a value (ex. 50) and it automatically fills the first and second columns appropriately.
Hi,
This can be done with VBA, which is the Excel scripting language.
Anyway, I've done this for you in the attached file.
This solution is powered by this code:
Sub Workbook_Open()
'Written by Haytham Amairah
'Last updated on 5/3/2018
Dim number As Integer
number = Application.InputBox("Please enter a number:")
Dim scell As Range
Set scell = Application.InputBox("Please select the starting cell:", , , , , , , 8)
For counter1 = 1 To number
Dim r1 As Integer
r1 = counter1 - 1
scell.Offset(r1).Value = counter1
Next counter1
For counter2 = number To 1 Step -1
Dim r2 As Integer
r2 = Abs(counter2 - number)
scell.Offset(r2, 1).Value = counter2
Next counter2
End SubPlease follow this https://support.office.com/en-us/article/automatically-run-a-macro-when-opening-a-workbook-1e55959b-e077-4c88-a696-c3017600db44 to learn how to inject your own workbook with this code!
I hope this helps you
Haytham
10 Replies
Hi,
This can be done with VBA, which is the Excel scripting language.
Anyway, I've done this for you in the attached file.
This solution is powered by this code:
Sub Workbook_Open()
'Written by Haytham Amairah
'Last updated on 5/3/2018
Dim number As Integer
number = Application.InputBox("Please enter a number:")
Dim scell As Range
Set scell = Application.InputBox("Please select the starting cell:", , , , , , , 8)
For counter1 = 1 To number
Dim r1 As Integer
r1 = counter1 - 1
scell.Offset(r1).Value = counter1
Next counter1
For counter2 = number To 1 Step -1
Dim r2 As Integer
r2 = Abs(counter2 - number)
scell.Offset(r2, 1).Value = counter2
Next counter2
End SubPlease follow this https://support.office.com/en-us/article/automatically-run-a-macro-when-opening-a-workbook-1e55959b-e077-4c88-a696-c3017600db44 to learn how to inject your own workbook with this code!
I hope this helps you
Haytham
- Hi,
I'm looking for someone to assist me with my excel project. I want a pop form to appear when a cell is selected, to assist enter data in that cell and several other specific cells, either in that worksheet or other sheets within the same workbook. Or create a series of promting messages requiring completion of few other cells if a certain cell is filled, or something similar to prompt users to fill in multiple data in different locations using just one form.
Thank you. - Hi,
I'm looking for someone to assist me with my excel project. I want a pop form to appear when a cell is select to assist enter data in that cell and several other specific cells either in that worksheet or other sheets within the same workbook.
Thank you. Nevermind I figured it out. I just changed the offset 1 value in "scell.Offset(r2, 1).Value = counter2" to however many columns I needed it to move over. Thanks again so much for your help. You're awesome!
Hi,
Nice to see you figured it out!
However, I've updated the code with an error handler to catch any error that may occur and prevent it from appearing so that it does not bother the end user.
This is the updated version:
Sub Workbook_Open()
'Written by Haytham Amairah
'Created: 5/3/2018
'Last updated: 5/4/2018
On Error GoTo handler
Dim number As Integer
number = Application.InputBox("Please enter a number:")
If number = False Then Exit Sub
Dim scell As Range
Set scell = Application.InputBox("Please select the starting cell:", , , , , , , 8)
For counter1 = 1 To number
Dim r1 As Integer
r1 = counter1 - 1
scell.Offset(r1).Value = counter1
Next counter1
For counter2 = number To 1 Step -1
Dim r2 As Integer
r2 = Abs(counter2 - number)
scell.Offset(r2, 2).Value = counter2
Next counter2
handler:
End SubRegards
