Solved

How to I create a popup for a form in Excel

Posted on 2013-06-27
25
214 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
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…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

821 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