Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

Show all records for edting on Excel from

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
ssmith94015
Asked:
ssmith94015
  • 8
  • 6
1 Solution
 
SiddharthRoutCommented:
Use 6 textboxes? Either populate then during run time or set their ControlSource property to the relevant cell.

Sid
0
 
SiddharthRoutCommented:
Here is a sample. The text boxes in userform1 are linked to cells A1:F1 in sheet1

Sid
Book1.xls
0
 
ssmith94015Author Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SiddharthRoutCommented:
Oh in that case use a Listview instead of listbox.

Let me create a sample for you.

Sid
0
 
SiddharthRoutCommented:
Please confirm the Excel Version 2003? 2007? 2010?

Sid
0
 
ssmith94015Author Commented:
OK, I appreciate it, thank you.
0
 
ssmith94015Author Commented:
2003
0
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
Sorry the snapshot...

Sid
Untitled.jpg
0
 
ssmith94015Author Commented:
I am looking at it now.  Will take me a bit to analyze.
0
 
SiddharthRoutCommented:
Sure

Sid
0
 
SiddharthRoutCommented:
First check if the userform loads correctly and you are not getting any error. We will then take it from there.

Sid
0
 
ssmith94015Author Commented:
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
 
ssmith94015Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now