• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

Import ComboBox value from Excel into MS ACCESS 2003 database via vba

I created a datasheet in excell that I email out to users.  I would like them to enter updates.  I password protect the sheet so that they can only make changes to the third column (updates only).  I ended up adding combo boxes to some of the rows, without knowing that the comboboxes are objects instead of regular validation.

I may have to hand enter the completed form values from persons that already sent the form back.  I have two questions, how do you read in the combobox value selected?  If you can not do this, can you suggest some VBA code to create a value list drop down in a cell that is based off another cell value range?  In the second approach, during the next collection, I would hide the value list and protect the sheet so users could not edit it.

I've attached a sample sheet to demonstrate the drop down (combobox) that does not currently import correctly.
test.xls
0
atljarman
Asked:
atljarman
  • 7
  • 6
2 Solutions
 
andrewssd3Commented:
How did you get the drop-down values into the combo boxes?  They look like form controls, but I would normally expect to see the input source set when you select Format control from the right click menu.  I can't find any useful properties for them when I read them as ControlSource objects in VBA
0
 
Jeffrey CoachmanMIS LiasonCommented:
I have requested that the Excel Zone be added to this Q...
0
 
atljarmanAuthor Commented:
Here is the VB that I used to create the drop down boxes:

        With .Range("C12:C12")
            Set xlComb = xlWS.DropDowns.Add(.Left, .Top, .Width, .Height)
        End With
        With xlComb
           .AddItem "Full-Time"
           .AddItem "Part-Time"
        End With

Sorry for the delay, family sickness and travel has kept me away.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
andrewssd3Commented:
This code reads the value from the dropdown and places it in the cell to the right.  Some cells have more than one dropdown and some are not properly initialised, hence the error handling.  If there is more than value in the dropdown, I have copied the value into the first empty cell to the right

The Dropdown object you have used is now a hidden object, presumably because MS want you to use ActiveX objects instead - I had never come across this one.  I'll post some code to add a more standard dropdown in a few minutes.

Sub ExtractDropdownText()

    Dim d As Excel.DropDown
    Dim vVal As Variant
    Dim rngOut As Excel.Range
    
    For Each d In ActiveSheet.DropDowns
        vVal = Empty
        On Error Resume Next
        vVal = d.List(d.ListIndex)
        On Error GoTo 0
        
        If Not IsEmpty(vVal) Then
            ' place the value in the first empty cell to the right of the dropdown
            ' (some cells have more than one dropdown)
            Set rngOut = d.TopLeftCell.Offset(0, 1)
            Do While Not (IsEmpty(rngOut.Value))
                Set rngOut = rngOut.Offset(0, 1)
            Loop
            rngOut.Value = vVal
        End If
    Next d
    
    
End Sub

Open in new window

0
 
andrewssd3Commented:
OK - I think this will give you a more standard dropdown box - the ActiveX version:
Public Sub AddDropdown(ByRef rngCell As Excel.Range, ByRef rngFill As Excel.Range)
    
    Dim o As Excel.OLEObject
    Dim cbx As MSForms.ComboBox
    
    With rngCell
        Set o = ActiveSheet.OLEObjects.Add( _
            ClassType:="Forms.ComboBox.1", Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
        Set cbx = o.Object
        
        With cbx
            .LinkedCell = rngCell.Address(True, True, xlA1, True)
            .ListFillRange = rngFill.Address(True, True, xlA1, True)
            .SpecialEffect = fmSpecialEffectFlat
            .BorderStyle = fmBorderStyleSingle
        End With
        
    End With

End Sub

Open in new window


You call this with for example
call AddDropdown(Range("H4"),Range("j5:j6"))

Open in new window

It will create the combo box as you did before, over the cell you specify in the first parameter.  The box is linked to the cell you specify, so whatever your users choose will be the value in the cell under the box - they can't change this directly as the box sits over it.  The second parameter is the listfillrange, which specifies the range from which the values for the dropdown are taken.
0
 
atljarmanAuthor Commented:
Hi,

I recieved an error with this line of the code:

cbx As MSForms.ComboBox

With this error: "User-defined type not defined"

Any thoughts on how to resolve?
0
 
atljarmanAuthor Commented:
andrewssd3,

What References in the Object Library do I need to have active for this function to work.  I think that I might not have the reference required.
0
 
andrewssd3Commented:
Yes you need to add a reference to Microsoft Forms 2.0 Object Library - I already had that.  You do it through Tools...References from the VBA editor, then find and check it in the list
0
 
atljarmanAuthor Commented:
Microsoft Forms 2.0 Object Library is not avaialble in the Library.  How do you add it?
0
 
andrewssd3Commented:
You should have it.  If you add a userform to your project it should automatically add the reference - then you can remove the userform and the reference stays..
0
 
atljarmanAuthor Commented:
Great News!  It works one time.  I imported the information and it showed up in my database like a charm.

The problem is that I will need to make a few hundred spreadsheets from a database.  If I run one excel spreadsheet and close the application and run one spreadsheet again, it works fine.  If I try to run two spreadsheets or run it more than once, there is a Global error.  

I think that something in the Public Sub code is not referenced correctly, such as Set to 'Nothing'.  I don't really know how to determine where the information needs to be referenced, and I spent about 4 hours trying, but this is where someone else has helped in the past.

I've attached a sample db that creates the sample spreadsheet from one table to mimic what I am trying to do.   I have asked it to make two excel (from the same data) spreadsheets to mimic the error I am getting.

Any suggestions would be greatly appreciated.  Sorry that I am not more up on which one of these variables is remaining open and causing the error.
test-wdropdown.mdb
0
 
andrewssd3Commented:
I have not been able to test this fully, but it looks like you need to change the AddDropDown calls to use the Worksheet range with .Range as you do in the code above
        Call AddDropdown(.Range("C12"), .Range("D5:D6"))

Open in new window

Also in Line 7 of the AddDropdown code, you need to change to
         Set o = rngCell.Worksheet.OLEObjects.Add( _
            ClassType:="Forms.ComboBox.1", Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)

Open in new window

That removes the dependency on having an activeworksheet.  I think that will work.
0
 
atljarmanAuthor Commented:
andrewssd3,

I will try this today and let you know if it works.  Thanks for your help.
0
 
atljarmanAuthor Commented:
This worked.  I created about 40 files and all have imported with the data from the drop down.  Thanks much.
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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