Need Help Understanding Database, Forms and Macro Concept

[achyfakey]

Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I am trying to wrap my head around how I can use Excel as a simple database with some simple reporting.

I want to create a form to use for the data entry. I need to manage about 60 pieces of data for each entry, so the standard Data > Forms kind of form will not work as it tops out at 32.

I figured out how to create a form in the Visual Basic Editor, but I don't know how to link the text boxes I created on the form with the data on my worksheet. So, for example, if I am on my worksheet and I envoke the data entry form, I want it to populate the text fields with the actual data for the particular entry I am on. That means populating 60 boxes on my form with data, which I will hand code, I just don't know how. And then I want to be able to change data on this form, click a button to move to the next entry and have the changes saved.

Basically, I need it to do what FileMaker does, but I need to do it in Excel with macros and buttons.

Do I need to define a database or table in Excel over the range of data on my worksheet? Is that necessary to allow me to add more entries or delete entries via macros?

Am I conceptualizing this the right way? I need to keep it simplified.

 

[Bob Greenblatt]

Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I
am trying to wrap my head around how I can use Excel as a simple database with
some simple reporting.

I want to create a form to use for the data entry. I need to manage about 60
pieces of data for each entry, so the standard Data > Forms kind of form will
not work as it tops out at 32.

I figured out how to create a form in the Visual Basic Editor, but I don't
know how to link the text boxes I created on the form with the data on my
worksheet. So, for example, if I am on my worksheet and I envoke the data
entry form, I want it to populate the text fields with the actual data for the
particular entry I am on. That means populating 60 boxes on my form with data,
which I will hand code, I just don't know how. And then I want to be able to
change data on this form, click a button to move to the next entry and have
the changes saved.

Basically, I need it to do what FileMaker does, but I need to do it in Excel
with macros and buttons.

Do I need to define a database or table in Excel over the range of data on my
worksheet? Is that necessary to allow me to add more entries or delete entries
via macros?

Am I conceptualizing this the right way? I need to keep it simplified.

You are conceptualizing it correctly. The way I often code these is to
assign a cell on the worksheet to represent the current database row. Then
in your code for the form, you populate the form fields based on this cell¹s
contents. You may have buttons on the form to move forward, backward, first
and last. These simply set the worksheet cell, and then rerun the code to
populate the form. On the OK button you will need to empty the form contents
into the appropriate row in the worksheet. This is a lot of code, but all
pretty straight forward. You may find some examples in many of the Excel
books in your local bookstore.

 

[achyfakey]

Well thanks for the quick response. I am doing a lot of online research, but the main thing for me right now is linking a text box's content on a form to a field in my database. So I want a box on my custom-created VisualBasic-made form that displays and can modify the content found in, say, this reference:

"DatabaseSheet!D" &RowIndex

Where "DatabaseSheet" = the name of the worksheet with my data base content

and "D" refers to column "D" obviously

and "RowIndex" I define (using Insert > Name > Define) as the cell on a sheet containing the number you describe as "assign a cell on the worksheet to represent the current database row".

How do I link that reference to the text box on a form that I hand-generate? I think this will open the floodgates for me.

 

[achyfakey]

That should read:

and "RowIndex" IS THE ENTRY ROW IN MY DATABASE THAT I define (using Insert > Name > Define) FROM a cell on a sheet containing the number you describe as "assign a cell on the worksheet to represent the current database row".

 

[Bob Greenblatt]

Well thanks for the quick response. I am doing a lot of online research, but
the main thing for me right now is linking a text box's content on a form to a
field in my database. So I want a box on my custom-created VisualBasic-made
form that displays and can modify the content found in, say, this reference:

"DatabaseSheet!D" &RowIndex

Where "DatabaseSheet" = the name of the worksheet with my data base content

and "D" refers to column "D" obviously

and "RowIndex" I define (using Insert > Name > Define) as the cell on a sheet
containing the number you describe as "assign a cell on the worksheet to
represent the current database row".

How do I link that reference to the text box on a form that I hand-generate? I
think this will open the floodgates for me.

As far as I know, you CAN¹T link the form field to a cell. You will have to
hand code moving each form field¹s contents to the correct field on the
worksheet and vice versa to load the form. Using a defined name to get the
row reference is fine. You'll just have to extract the value in your code.