We help IT Professionals succeed at work.

using VBA/Excel Form to maintain large data base

Don OHara
Don OHara asked
on
49 Views
Last Modified: 2020-05-19
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
Comment
Watch Question

Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
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.
gowflowPartner
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
Partner
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
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)!
gowflowPartner
CERTIFIED EXPERT

Commented:
@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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.