May 03 2018 07:05 AM
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.
May 03 2018 08:13 AM - edited May 03 2018 08:22 AM
SolutionHi,
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 Sub
Please follow this link to learn how to inject your own workbook with this code!
I hope this helps you
Haytham
May 03 2018 01:18 PM
Thank you so much for your solution and doing the work. It is very very close to what I need. However, the countdown occurs in the second column. I can't figure out how to get the countdown to populate in the third column instead of the second column. So how do I change the column that it populates?
May 03 2018 01:39 PM
Hi Haytham,
Sorry, my browser hangs for a while and I not intentionally clicked on Best response, remove it after that. Perhaps that post is really Best response, but that's not my decision.
May 03 2018 01:55 PM
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!
May 03 2018 08:10 PM
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 Sub
Regards
May 03 2018 08:15 PM
Hi Sergei,
No problem.
It really isn't the best response, it lacked an error handler!
Dec 31 2018 09:57 AM
Dec 31 2018 10:52 AM
Sep 24 2019 02:16 PM
@Haytham Amairah I am trying to access room list data from the second tab of my excel workbook by pressing a command button on the first tab. but the Room list in the second tab should appear as pop up window. is it possible in excel?
Sep 26 2019 08:14 AM
Hi,
It's best to post this question in a separate conversation in this community because it's a different topic.
Regards
May 03 2018 08:13 AM - edited May 03 2018 08:22 AM
SolutionHi,
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 Sub
Please follow this link to learn how to inject your own workbook with this code!
I hope this helps you
Haytham