Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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