• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

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!!!
0
brasiman
Asked:
brasiman
  • 13
  • 12
1 Solution
 
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
 
brasimanAuthor Commented:
Then how do i get the button on my spreadsheet/workbook?
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

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

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 13
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now