?
Solved

Show all records for edting on Excel from

Posted on 2011-02-14
14
Medium Priority
?
271 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
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
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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