Solved

How to I create a popup for a form in Excel

Posted on 2013-06-27
25
194 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 45

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

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 45

Expert Comment

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

2003
0
 
LVL 45

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 45

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 45

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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 45

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 45

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 45

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 45

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 45

Expert Comment

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

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
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 the scrolling table in Microsoft Excel using the INDEX function.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now