M
mpysr
Hi everybody. Well, I've spent the requisite 4+ hours looking in
manuals, searching the Internet and this group, and I'm totally
confused about how Excel works with regard to "databases" and forms
and macros. I have moderate understanding of spreadsheets (e.g.
lookup and formulas), but what's got me lost is defining areas and
forms and getting the macro (button) to bring up a form. I'm using
Excel 2003.
So I'll start with some really basic stuff. In a spreadsheet, I've
got the following 4column x 2row entries:
NAME CITY PRICE DATE
x y 6 7
I've also done the following: Selected the first two rows and columns
(2x2) and then invoked Insert/Name/Define, and entered "First2". Then
I selected the other two rows and columns (2x2) and invoke Insert/Name/
Define, and entered "Second2".
What have I created? Are these considered "databases"? A "list
table"? (as a Microsoft manual suggests) Or what?
There is a pull-down at the upper left which has, in addition to the
current cell (e.g. D8), two additional entries: "First2" and
"Second2". Is that the only place they are listed, or is there some
other place where these things are shown (and can be edited or
deleted)?
If I highlight the cell containing the "y", or the one below it, and
invoke Data/Form, up pops a dialogue with:
NAME, CITY, PRICE, DATE
Why am I seeing PRICE and DATE since it's not part of the Named-
Defined region?.
Why does that happen for the cell below the "y" but not for the cell
below that? (also for the cell to the right of the "7", and the one
below that). Shouldn't the region be tightly defined so that the Form
only pops up when you are inside it?
I also created much further to the right a 2x2 block and Defined a
Name, and if I select the a cell in the lower row, and then invoke
Data/Form, then I get a clean 2-entry Form. So I'm of the impression
that Forms are contextual (to a degree), and that you can have
multiple Forms on a single sheet? Is that correct? Do they have
names?
Setting that aside, which isn't critical but is mysterious, I'll
accept for now that I've got a form (or 2 or 3) of some sort active
here.
HERE'S THE BIG PROBLEM: I want to have a button that runs a macro
that pops up a form for data entry. I've set the macro security to
Medium, invoked Tools/Macro/RecordNewMacro (naming it "LoadDataForm"),
then with a cell highlighted within the "First2" data block, invoked
Data/Form, and up popped the box. I click on Close, and then stop the
macro recording.
Checking the macro, with the name LoadDataForm, there is something,
but not much:
Sub LoadDataForm ()
ActiveSheet.ShowDataForm
End Sub
When I try and run it, I get the error message:
ShowDataForm method of class Worksheet failed.
I've also received that message on a sheet with only one region Name/
Define(d), just in case there is a problem with multiple Form(s) on a
sheet, if that's possible.
I'm open to anything. Do I put into the macro something like:
Range("B5").Select and then use a method for ActiveCell to get the
Form? Tried ActiveSheet.ShowDataForm after the Range-Select, but
that didn't work.
Are there any webpages that tell you *exactly* what to do to create a
macro for a Form? I've been following tips over at PC Answers, but
I'm afraid I need explicit instructions, for every single click.
(e.g. instructions like "Click inside the data you have entered" are
imprecise. Inside means...? Excluding the label row?)
manuals, searching the Internet and this group, and I'm totally
confused about how Excel works with regard to "databases" and forms
and macros. I have moderate understanding of spreadsheets (e.g.
lookup and formulas), but what's got me lost is defining areas and
forms and getting the macro (button) to bring up a form. I'm using
Excel 2003.
So I'll start with some really basic stuff. In a spreadsheet, I've
got the following 4column x 2row entries:
NAME CITY PRICE DATE
x y 6 7
I've also done the following: Selected the first two rows and columns
(2x2) and then invoked Insert/Name/Define, and entered "First2". Then
I selected the other two rows and columns (2x2) and invoke Insert/Name/
Define, and entered "Second2".
What have I created? Are these considered "databases"? A "list
table"? (as a Microsoft manual suggests) Or what?
There is a pull-down at the upper left which has, in addition to the
current cell (e.g. D8), two additional entries: "First2" and
"Second2". Is that the only place they are listed, or is there some
other place where these things are shown (and can be edited or
deleted)?
If I highlight the cell containing the "y", or the one below it, and
invoke Data/Form, up pops a dialogue with:
NAME, CITY, PRICE, DATE
Why am I seeing PRICE and DATE since it's not part of the Named-
Defined region?.
Why does that happen for the cell below the "y" but not for the cell
below that? (also for the cell to the right of the "7", and the one
below that). Shouldn't the region be tightly defined so that the Form
only pops up when you are inside it?
I also created much further to the right a 2x2 block and Defined a
Name, and if I select the a cell in the lower row, and then invoke
Data/Form, then I get a clean 2-entry Form. So I'm of the impression
that Forms are contextual (to a degree), and that you can have
multiple Forms on a single sheet? Is that correct? Do they have
names?
Setting that aside, which isn't critical but is mysterious, I'll
accept for now that I've got a form (or 2 or 3) of some sort active
here.
HERE'S THE BIG PROBLEM: I want to have a button that runs a macro
that pops up a form for data entry. I've set the macro security to
Medium, invoked Tools/Macro/RecordNewMacro (naming it "LoadDataForm"),
then with a cell highlighted within the "First2" data block, invoked
Data/Form, and up popped the box. I click on Close, and then stop the
macro recording.
Checking the macro, with the name LoadDataForm, there is something,
but not much:
Sub LoadDataForm ()
ActiveSheet.ShowDataForm
End Sub
When I try and run it, I get the error message:
ShowDataForm method of class Worksheet failed.
I've also received that message on a sheet with only one region Name/
Define(d), just in case there is a problem with multiple Form(s) on a
sheet, if that's possible.
I'm open to anything. Do I put into the macro something like:
Range("B5").Select and then use a method for ActiveCell to get the
Form? Tried ActiveSheet.ShowDataForm after the Range-Select, but
that didn't work.
Are there any webpages that tell you *exactly* what to do to create a
macro for a Form? I've been following tips over at PC Answers, but
I'm afraid I need explicit instructions, for every single click.
(e.g. instructions like "Click inside the data you have entered" are
imprecise. Inside means...? Excluding the label row?)