Link to home
Start Free TrialLog in
Avatar of Don OHara
Don OHara

asked on

using VBA/Excel Form to maintain large data base

In Excel, using VBA to display a form for a large single data record (60 items), some items can be updated by the user.  How to create the display form: how user would select record? .

same0l file is attached. Shows raw data grid and a sample display grid for one record.

1) How do I create a form for a single record display? How does user select a record?  Add a search feature?  How to specify some fields as user updatable.

Thank you.
SampleExcelDatafileDOHprojct.xlsx
Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

Welcome to "Excel Hell" - the art and misguided science of trying top use a spreadsheet program to do database work.  Access can do a much better/cleaner job of it.  That's what it was designed for.  Those that went to Access for database processing and used Excel as the input/reporting medium never looked back.

But feel free to enter the depths of "Excel Hell" at your own peril.
Is this what you want ??


Private Sub ListBox1_Click()
Dim WSDisplay As Worksheet, WSData As Worksheet
Dim lRow As Long

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

Set WSDisplay = Sheets("Display Grid ")
Set WSData = Sheets("Rqw data records. ")
lRow = ListBox1.ListIndex + 2

WSDisplay.Range("D3:D20").ClearContents
WSDisplay.Range("G3:G7").ClearContents

'---> Display Record
'Name and Address
WSDisplay.Range("D3") = WSData.Cells(lRow, "A")
WSDisplay.Range("G3") = WSData.Cells(lRow, "B")
WSDisplay.Range("G4") = WSData.Cells(lRow, "C")
WSDisplay.Range("G5") = WSData.Cells(lRow, "D")
WSDisplay.Range("G6") = WSData.Cells(lRow, "E")
WSDisplay.Range("G7") = WSData.Cells(lRow, "F")

'Phone fax website
WSDisplay.Range("D4") = WSData.Cells(lRow, "G")
WSDisplay.Range("D5") = WSData.Cells(lRow, "H")
WSDisplay.Range("D6") = WSData.Cells(lRow, "I")
WSDisplay.Range("D7") = WSData.Cells(lRow, "J")

'Figures
WSDisplay.Range("D9") = WSData.Cells(lRow, "L")
WSDisplay.Range("D10") = WSData.Cells(lRow, "M")
WSDisplay.Range("D11") = WSData.Cells(lRow, "N")
WSDisplay.Range("D12") = WSData.Cells(lRow, "O")

WSDisplay.Range("D13") = WSData.Cells(lRow, "P")
WSDisplay.Range("D14") = WSData.Cells(lRow, "Q")
WSDisplay.Range("D15") = WSData.Cells(lRow, "R")
WSDisplay.Range("D16") = WSData.Cells(lRow, "S")
WSDisplay.Range("D17") = WSData.Cells(lRow, "T")
WSDisplay.Range("D18") = WSData.Cells(lRow, "U")

WSDisplay.Range("D20") = WSData.Cells(lRow, "K")


'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub

Open in new window



Please check the attached workbook. Simply select a company in the list and check the results.
Gowflow
SampleExcelDatafileDOHprojct--V01-.xlsm
Avatar of Don OHara
Don OHara

ASKER

Thanks gowflow. This has given me a great start to understanding listbox controls.
Questions:  1.  can I have this grid display  (in a separate window) while I still view the original data sheet?
                        if so, can the Listbox control be column A of the data sheet (rather an a separate listing)  This way, as I scan my data sheet, I can click on the Firm Name in Column A to activate the detail display (in the separate window)?

2. I want to make one field (Notes) updatable. A control that updates the original data field if I change the data.

Thanks again. Appreciate all your help.
Don
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
See..... I told ya......  Access is built for Form work and massive, but simple data manipulation and processing...

Keep putting off the move to Access as long as you want to, but you'll eventually see the light (after experiencing hell)!
@Mark
Please have constructive input rather than shooting down an other software as maybe not in your field of expertise. A lot of people uses Excel and same can be handled quite well when one is knowlegable.

Access has its flops also I can tell you many as developing for over 40 years, if you want to have a solid database handling its rather VB the way to go rather than Access for Form/Module/Classes/API etc... using Access database.

We will sure not go in this debate as I believe is totally out of scope here.

Gowflow