?
Solved

How to I create a popup for a form in Excel

Posted on 2013-06-27
25
Medium Priority
?
235 Views
Last Modified: 2013-06-27
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
Comment
Question by:brasiman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 12
25 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39282733
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
 

Author Comment

by:brasiman
ID: 39282755
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
 

Author Comment

by:brasiman
ID: 39282765
Then how do i get the button on my spreadsheet/workbook?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Expert Comment

by:Martin Liss
ID: 39282798
Here's a demo project and here's a picture of where the controls come from.
controlsBook2.xlsm
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39282811
Sorry in 2003 it's a little different.

2003
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39282814
And that seems to be an ActiveX control.
0
 

Author Comment

by:brasiman
ID: 39282831
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39282852
Why don't you attach your project.
0
 

Author Comment

by:brasiman
ID: 39282865
See attached. We just started it.
DataEntry.xls
0
 

Author Comment

by:brasiman
ID: 39282871
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39282892
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
 

Author Comment

by:brasiman
ID: 39282907
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
 

Author Comment

by:brasiman
ID: 39282920
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
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 39282924
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
 

Author Comment

by:brasiman
ID: 39282926
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39282928
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39282932
Here's a workbook with the macro installed.
DataEntry.xls
0
 

Author Comment

by:brasiman
ID: 39282948
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39282956
Sorry. Go to Tools|Macro|Macros on the sheet.
Oops
BTW you don't need Dim lngRow As Long in the macro.
0
 

Author Comment

by:brasiman
ID: 39282962
Cool, that worked. So the last thing is it possible to create a button to open the form, instead of a shortcut?
0
 

Author Comment

by:brasiman
ID: 39282975
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
 

Author Closing Comment

by:brasiman
ID: 39282978
Thanks!
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39282980
Okay, done.
DataEntry.xls
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39282984
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
 

Author Comment

by:brasiman
ID: 39282986
I really appreciate it Marty!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question