Solved

How to I create a popup for a form in Excel

Posted on 2013-06-27
25
220 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
  • 13
  • 12
25 Comments
 
LVL 46

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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 46

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 46

Expert Comment

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

2003
0
 
LVL 46

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 46

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 46

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 46

Accepted Solution

by:
Martin Liss earned 500 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 46

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 46

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 46

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 46

Expert Comment

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

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

828 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