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

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

Mwandingi_03
Copper Contributor
Dec 31, 2018
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.
Mwandingi_03
Copper Contributor
Dec 31, 2018
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.

blackwelldanny

Copper Contributor

May 03, 2018

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!

Haytham Amairah

Silver Contributor

May 04, 2018

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

Fayarus
Copper Contributor
Sep 24, 2019
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?

SergeiBaklan
Diamond Contributor
May 03, 2018
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.
- Haytham Amairah
  Silver Contributor
  May 04, 2018
  Hi Sergei,
  
  No problem.
  It really isn't the best response, it lacked an error handler!
blackwelldanny
Copper Contributor
May 03, 2018
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?

Forum Discussion

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

Resources