Create a pop up box for user data input that auto formulates specific cells

[blackwelldanny]

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. 

[#2 · Accepted solution]

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 Sub

Please follow this link to learn how to inject your own workbook with this code!

I hope this helps you

Haytham

[#3]

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?

[#4]

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.

[#5]

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!

[#6]

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

[#7]

Hi Sergei,

No problem.

It really isn't the best response, it lacked an error handler!

[#8]

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.

[#9]

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.

[#10]

@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?

[#11]

@Fayarus

Hi,

It's best to post this question in a separate conversation in this community because it's a different topic.

Regards