Solved

Show all records for edting on Excel from

Posted on 2011-02-14
14
263 Views
Last Modified: 2012-05-11
Is there a control or way to show all the fields in an underlying named range in an Excel form?  I want to control edits to fields and only let users see forms, but is there a way to show, say the range has six rows, all six rows on one form that can be edited?
0
Comment
Question by:ssmith94015
  • 8
  • 6
14 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34889720
Use 6 textboxes? Either populate then during run time or set their ControlSource property to the relevant cell.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34889757
Here is a sample. The text boxes in userform1 are linked to cells A1:F1 in sheet1

Sid
Book1.xls
0
 

Author Comment

by:ssmith94015
ID: 34889871
I can get the data if it is all on one row, but perhaps I was not clear, I need to have the form reflect all the rows.  that is, if there are six rows, I need the form to show all six rows of data.  Actually, I think if I use a List Box - which does show all the rows in the data range, but I want the user to click on one of the rows have have that data only show in the text boxes.  Attached is the code I have started with and also an image of the form.
Private Sub lblMarketData_Click()
Dim lngItem As Long
                If lstMarketData.Selected(lngItem) = True Then
                        Me.txtMarketData = lstMarketData.List(lngItem)
                        Me.txtLookupValue = lstMarketData.List(lngItem)
                        Me.txtColumnNumber = lstMarketData.List(lngItem)
                        Me.txtRangeName = lstMarketData.List(lngItem)
                End If
End Sub

Open in new window

MarketDataForm.doc
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34889971
Oh in that case use a Listview instead of listbox.

Let me create a sample for you.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34889981
Please confirm the Excel Version 2003? 2007? 2010?

Sid
0
 

Author Comment

by:ssmith94015
ID: 34889991
OK, I appreciate it, thank you.
0
 

Author Comment

by:ssmith94015
ID: 34889993
2003
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 34890381
Try this. Sample file attached. I created this in Excel 2007 but save the file as 2003 format. So if you face any problem, let me know. Snapshot also attached.

Sid

Code Used

Private Sub UserForm_Initialize()
    Dim lvItem As ListItem
    
    ListView1.ColumnHeaders.Add , , "Col A"
    ListView1.ColumnHeaders.Add , , "Col B"
    ListView1.ColumnHeaders.Add , , "Col C"
    ListView1.ColumnHeaders.Add , , "Col D"
    ListView1.ColumnHeaders.Add , , "Col E"
    ListView1.ColumnHeaders.Add , , "Col F"
    
    ListView1.View = lvwReport
    ListView1.FullRowSelect = True
    ListView1.ListItems.Clear
    
    For i = 1 To 6
        Set lvItem = ListView1.ListItems.Add()
        lvItem.Text = Sheets("Sheet1").Range("A" & i).Value
        
        ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Sheets("Sheet1").Range("B" & i).Value)
        ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Sheets("Sheet1").Range("C" & i).Value)
        ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Sheets("Sheet1").Range("D" & i).Value)
        ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Sheets("Sheet1").Range("E" & i).Value)
        ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Sheets("Sheet1").Range("F" & i).Value)
    Next i
End Sub

Private Sub ListView1_Click()
    TextBox1.Text = ListView1.SelectedItem.Text
    TextBox2.Text = ListView1.ListItems(ListView1.SelectedItem.Index).ListSubItems(1).Text
    TextBox3.Text = ListView1.ListItems(ListView1.SelectedItem.Index).ListSubItems(2).Text
    TextBox4.Text = ListView1.ListItems(ListView1.SelectedItem.Index).ListSubItems(3).Text
    TextBox5.Text = ListView1.ListItems(ListView1.SelectedItem.Index).ListSubItems(4).Text
    TextBox6.Text = ListView1.ListItems(ListView1.SelectedItem.Index).ListSubItems(5).Text
End Sub

Open in new window

Book1.xls
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34890387
Sorry the snapshot...

Sid
Untitled.jpg
0
 

Author Comment

by:ssmith94015
ID: 34890428
I am looking at it now.  Will take me a bit to analyze.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34890445
Sure

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34890460
First check if the userform loads correctly and you are not getting any error. We will then take it from there.

Sid
0
 

Author Closing Comment

by:ssmith94015
ID: 34890674
When I open your it works perfectly and I understand what is going on.  When I put the code in my workbooks, I get an error about unsafe code.  I checked the libraries to be sure I had all the references and I noticed on yours you have SP6 - out system does not even have the needed library available!  Since this has to go to another office (in another state) and I do not know what they have, I am unable to use the feature.  But I really do like this approach and I can use it with another client, but will have to think of something else here.  Thank you for your work, I am really impressed as I would never have gotten this far without your help!
0
 

Author Comment

by:ssmith94015
ID: 34890777
Ok, ok, I FOUND the MSCOMCTL.OCX library (Brower is wonderful) and have added the refrence for Micjrosfot Windows Common Controls 6.0(SP6) to my libary.  So, when I search in the library, it  now shows up!  I am getting a data type mismatch, but am going to post that as another questions.  Thank you, am making better progress than I originally thought!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

705 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now