How to I create a popup for a form in Excel

In Excel 2003, I have 5 column headers. January-May. I want to create a form. I know how to bring up the form, but i have to highlight the data each time, then go to Data then Form each time i need to enter more data. Is there a way to create a button on my worksheet that automatically does that for me. I could call the button Enter Data, and it brings up this form for me? Are there any other cool things i could do with the form too, to make it easy for my data entry girl? Right now, my spreadsheet is called DataEntry and the worksheet is called Sheet1. Thanks!!!
brasimanAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
In that case change the macro to this which assumes that column A will always have the same or more rows that any of the other 4 columns.
Sub ShowDataForm()
    Dim lngLastRow As Long
    Dim lngRow As Long
    
    lngLastRow = Range("A65536").End(xlUp).Row
    
    Range("A2:E" & lngLastRow).Select
    ActiveSheet.ShowDataForm
End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
Sure. Go to Visual Basic code and add a UserForm. Then on the sheet add a forms control or ActiveX button. For the former you would assign a macro and for the latter you would use the control's Click event, but in either case you would add a UserForm1.Show line.
0
 
brasimanAuthor Commented:
Hi MartinLiss. Thanks for the quick reply! I am a little lost. Sorry. So i went to visual basics, created a form. I then dragged over a command button. I then right clicked the button and said view code. This is what it looks like:

Private Sub CommandButton1_Click()

End Sub

do i put UserForm1.Show in the middle above End Sub? Is that what you mean? Thanks.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
brasimanAuthor Commented:
Then how do i get the button on my spreadsheet/workbook?
0
 
Martin LissOlder than dirtCommented:
Here's a demo project and here's a picture of where the controls come from.
controlsBook2.xlsm
0
 
Martin LissOlder than dirtCommented:
Sorry in 2003 it's a little different.

2003
0
 
Martin LissOlder than dirtCommented:
And that seems to be an ActiveX control.
0
 
brasimanAuthor Commented:
Thanks MartinLiss. You're going to hate me. So that REALLY helps. Now i just need the Form to pop up so i don't have to highlight the data, then go up to Data, the Form. I attached a screenshot what i see. That is what i need to pop up when i click a button. Is that possible?
Form.bmp
0
 
Martin LissOlder than dirtCommented:
Why don't you attach your project.
0
 
brasimanAuthor Commented:
See attached. We just started it.
DataEntry.xls
0
 
brasimanAuthor Commented:
In Excel 2003, i can go to Data/Form, and a entry form pops up. See screenshot i attached. Then i just put in the data into the right places.
0
 
Martin LissOlder than dirtCommented:
Okay you can if you want forget the userform. I actually didn't know about Data|Form but now that I do here's a macro I recorded that you can use to bring up the data form.

Sub ShowDataForm()
    Range("A2:E5").Select
    ActiveSheet.ShowDataForm
End Sub

Open in new window


You can automate the running of the macro by going to Tools|Macro|Options… and assign some letter like 'F' to the shortcut key and then after that all you'd need do is to type Ctrl+f

Do you need the macro to be variable? In other words might there be more or less columns or more or less rows?
0
 
brasimanAuthor Commented:
Thank you! I will try that now. The columns will be fixed. The rows will vary. I could do down 50 rows, or 10,000 rows.
0
 
brasimanAuthor Commented:
Do I choose CommandButton? Or do i go to visual basics and create a form. How and where would i put that code you gave me?
0
 
brasimanAuthor Commented:
Do I choose CommandButton? Or do i go to visual basics and create a form. How and where would i put that code you gave me?
0
 
Martin LissOlder than dirtCommented:
We cross-posted. Go into Visual Basic and Add Module. Put the macro there. Other than optionally assigning the shortcut key there's nothing else that needs to be done.
0
 
Martin LissOlder than dirtCommented:
Here's a workbook with the macro installed.
DataEntry.xls
0
 
brasimanAuthor Commented:
Ok, so i have the marco module created. You mentioned to go to Tools | Macro | Options so i can do Ctrl and a letter for a shortcut. If i go to Tools | Macro, i don't see options. Can i create a button for it and a shortcut? How would I assign a button this? And a shortcut?
0
 
Martin LissOlder than dirtCommented:
Sorry. Go to Tools|Macro|Macros on the sheet.
Oops
BTW you don't need Dim lngRow As Long in the macro.
0
 
brasimanAuthor Commented:
Cool, that worked. So the last thing is it possible to create a button to open the form, instead of a shortcut?
0
 
brasimanAuthor Commented:
Nevermind, i got it. I just created a button, then put in the code you gave me at the beginning, ActiveSheet.ShowDataForm. That works. Thank you for your help MartinLiss!!! I appreciate you sticking with me. :) Thanks!
0
 
brasimanAuthor Commented:
Thanks!
0
 
Martin LissOlder than dirtCommented:
Okay, done.
DataEntry.xls
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
 
brasimanAuthor Commented:
I really appreciate it Marty!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.