Solved

Show all records for edting on Excel from

Posted on 2011-02-14
14
264 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
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 …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

895 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

14 Experts available now in Live!

Get 1:1 Help Now