Forum Discussion

blackwelldanny's avatar
blackwelldanny
Copper Contributor
May 03, 2018
Solved

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 colum...
excel
  • Haytham Amairah's avatar
    Haytham Amairah
    May 03, 2018

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

blackwelldanny's avatar
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's avatar
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's avatar
    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?

Resources