Access VBA to transfer combo box values into Access Table

[JFKJr]

Hello everyone, the following is the Access VBA code which opens an excel spreadsheet and creates combo boxes dynamically.

And whenever a user selects a value in a combo box, I am trying to pass the selected value into the corresponding cell in excel spreadsheet and then importing the excel spreadsheet into an Access Table.

But the following code is generating "Run-time error 91: Object variable or With block variable not set" error at line #18 in "Class1" class module.

1. Created the following Access VBA module:

Public eXL As New eventsXL
 
Function Create_ComboBoxes()
Dim OLEObj As OLEObject, OLEObj1 As OLEObject
Dim myRng As Range, myCell As Range, myRng1 As Range, myCell1 As Range
 
With eXL
If .XL Is Nothing Then Set .XL = New Excel.Application
.XL.Visible = True
.XL.Interactive = True
Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
Set .WS = .WB.Worksheets("Example")
.WS.Activate
 
.XL.CommandBars("Control Toolbox").Visible = False
.WS.OLEObjects.Delete
 
Set myRng = .WS.Range("E2:E12")
For Each myCell In myRng.Cells
With myCell
    .NumberFormat = ";;;" 'hide the value in the cell
    Set OLEObj = .Parent.OLEObjects.Add _
                (ClassType:="Forms.ComboBox.1", Link:=False, _
                DisplayAsIcon:=False, _
                Top:=.Top, _
                Left:=.Left, _
                Width:=.Width, _
                Height:=.Height)
End With
.Abc OLEObj.Object
Next myCell
End With
End Function
 
Function Export_ExtractedPolicyList()
Dim excelFile As String, tableName As String
excelFile = "C:\Book1.xls"
tableName = "Example"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName, excelFile, True
End Function

2. Created the following "eventsXL" class module:

Public WithEvents XL As Excel.Application
Public WithEvents WB As Excel.Workbook
Public WithEvents WS As Excel.Worksheet
Private myObj As New VBA.Collection
Private myObj1 As New VBA.Collection
 
Public Property Get myComboBoxes() As VBA.Collection
    Set myComboBoxes = myObj
End Property
 
Public Property Get myNewComboBoxes() As VBA.Collection
    Set myNewComboBoxes = myObj1
End Property
 
Public Function Abc(obj As Variant) As Class1
    Set Abc = New Class1
    Set Abc.myComboBox = obj
    Me.myComboBoxes.Add Abc
End Function

3. Created the following "Class1" class module:

Public WithEvents myOLE As MSForms.ComboBox
Public objCell As eventsXL
 
Public Property Get myComboBox() As MSForms.ComboBox
Set myComboBox = myOLE
End Property
 
Public Property Set myComboBox(ByRef myCombo As MSForms.ComboBox)
Set myOLE = myCombo
myOLE.AddItem "A"
myOLE.AddItem "B"
myOLE.AddItem "C"
myOLE.AddItem "D"
End Property
 
Public Sub myOLE_Change()
Dim Item As String
objCell.XL.ActiveCell.Value = myOLE.Text
End Sub

[Stewart Ross]

Hi again JFKJr. In line 18 of Class1 you are referring to object objCell which has not been initialised, so it would look like the error message simply reflects that fact. It is public to the module, but the public declaration does not initialise the object variable at all. You will need to set objCell appropriately to resolve this.

I note that you do not use class initialise or terminate procedures, which are usually used to set and cleanup class object variables appropriately.

-Stewart

[JFKJr]

Hi again JFKJr. In line 18 of Class1 you are referring to object objCell which has not been initialised, so it would look like the error message simply reflects that fact. It is public to the module, but the public declaration does not initialise the object variable at all. You will need to set objCell appropriately to resolve this.

I note that you do not use class initialise or terminate procedures, which are usually used to set and cleanup class object variables appropriately.

-Stewart

Hello Stewart, nice to see you again.

I changed the code a little bit. The following Access VBA code opens a new excel spreadsheet and creates combo boxes dynamically in a given range of cells using "Create_ComboBo xes" proc below.

And whenever user selects a value in the combo box, the value should be inserted into the corresponding cell in which the combo box is present.

And when I run "Export_Extract edPolicyList" proc below, the excel spreadsheet should be imported into an Access Table which stores the combo box values that user has selected.

But the following code is unable to insert the user selected combo box value into the corresponding cell. The active cell address is displaying empty at line #18 in "Class1" class module.

Please kindly let me where I am doing wrong?

Thanks.

1. Access VBA module:

Public eXL As New eventsXL
 
Function Create_ComboBoxes()
Dim OLEObj As OLEObject, OLEObj1 As OLEObject
Dim myRng As Range, myCell As Range, myRng1 As Range, myCell1 As Range
Dim cellAddr As String, curChar As String, actualAddr As String, X As Integer
 
With eXL
If .XL Is Nothing Then Set .XL = New Excel.Application
.XL.Visible = True
.XL.Interactive = True
Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
Set .WS = .WB.Worksheets("Example")
.WS.Activate
 
.XL.CommandBars("Control Toolbox").Visible = False
.WS.OLEObjects.Delete
 
Set myRng = .WS.Range("E2:E12")
For Each myCell In myRng.Cells
With myCell
    actualAddr = " "
    .NumberFormat = ";;;" 'hide the value in the cell
    Set OLEObj = .Parent.OLEObjects.Add _
                (ClassType:="Forms.ComboBox.1", Link:=False, _
                DisplayAsIcon:=False, _
                Top:=.Top, _
                Left:=.Left, _
                Width:=.Width, _
                Height:=.Height)
End With
 
cellAddr = myCell.Address
 
For X = 1 To Len(cellAddr)
    curChar = Mid(cellAddr, X, 1)
    If InStr("$", curChar) = 0 Then actualAddr = _
           actualAddr & curChar
Next X
.Abc OLEObj.Object, actualAddr
Next myCell
End With
End Function
 
Function Export_ExtractedPolicyList()
Dim excelFile As String, tableName As String
excelFile = "C:\Book1.xls"
tableName = "Example"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName, excelFile, True
End Function
 
Public Function ImportExcelSheet(comboValue As String, ceAddr As String)
eXL.XL.Range(ceAddr).Value = comboValue
End Function

2. "eventsXL" class module:

Public WithEvents XL As Excel.Application
Public WithEvents WB As Excel.Workbook
Public WithEvents WS As Excel.Worksheet
Private myObj As New VBA.Collection
Private myObj1 As New VBA.Collection
Public cellAddress As String
 
Public Property Get myComboBoxes() As VBA.Collection
    Set myComboBoxes = myObj
End Property
 
Public Property Get myNewComboBoxes() As VBA.Collection
    Set myNewComboBoxes = myObj1
End Property
 
Public Function Abc(obj As Variant, cAddr As String) As Class1
    cellAddress = cAddr
    Set Abc = New Class1
    Set Abc.myComboBox = obj
    Me.myComboBoxes.Add Abc
End Function
 
Private Sub XL_WorkbookBeforeClose(ByVal WB1 As Excel.Workbook, Cancel As Boolean)
With XL.Workbooks
XL.Quit
Set eXL.XL = Nothing
Set eXL.WB = Nothing
Set eXL.WS = Nothing
Set eXL = Nothing
End With
Exit Sub
End Sub

3. "Class1" class module:

Public WithEvents myOLE As MSForms.ComboBox
Public cXL As New eventsXL
 
Public Property Get myComboBox() As MSForms.ComboBox
Set myComboBox = myOLE
End Property
 
Public Property Set myComboBox(ByRef myCombo As MSForms.ComboBox)
Set myOLE = myCombo
myOLE.AddItem "A"
myOLE.AddItem "B"
myOLE.AddItem "C"
myOLE.AddItem "D"
End Property
 
Public Sub myOLE_Change()
Dim Item As String
MsgBox "" & cXL.cellAddress
ImportExcelSheet myOLE.Text, cXL.cellAddress
End Sub

[Stewart Ross]

Well, you have at least one circular relationship between your class modules.

In line 18 of Class1 you are referring to object cXL, which has been initialised this time in the public declaration. Its type is EventsXL. But, if you look at class module EventsXL you will find a function header at line 16 which returns a value of type Class1. Class1 cannot depend on EventsXL if EventsXL also depends on Class1.

I have not skimmed for other errors - this one is big enough I'm sorry to say. Redesign 2 is inevitable.

-Stewart

[FishVal]

Well, you have at least one circular relationship between your class modules.

In line 18 of Class1 you are referring to object cXL, which has been initialised this time in the public declaration. Its type is EventsXL. But, if you look at class module EventsXL you will find a function header at line 16 which returns a value of type Class1. Class1 cannot depend on EventsXL if EventsXL also depends on Class1.

I have not skimmed for other errors - this one is big enough I'm sorry to say. Redesign 2 is inevitable.

-Stewart

Hello, Stewart.

As soon as I've taken part in code creation , I'd like to clarify situation.

• Class eventsXL contains VBA.Collection of Class1 objects. Method eventsXL.Abc() adds an object to the collection.
• At the same time Class1 contains reference to parent eventsXL object to ensure two-way referencing.
• cXL was supposed to be instantiated in eventsXL.Abc() immediately after creation of Class1 object.

Unfortunately OP has done nothing to improve code styling. It is still that messed, kludgy and with bad naming as it was last time I've seen it, maybe even more. All that makes it difficult to understand.

Regards.
Fish

[FishVal]

@JFKJr

Sorry, comrade.

Last time I just didn't want to spoil your triumph with those silly thoughts about code style and naming conventions. Now you have a not that long peace of code that has already messed you to say nothing about somebody seeing this first time. ;)

You code is so messed and bad styled that personally I would let it RIP and recode from scratch. Keeping in mind straight and flexible logic, helpful comments and meaningful naming.

Regards,
Fish

[JFKJr]

@JFKJr

Sorry, comrade.

Last time I just didn't want to spoil your triumph with those silly thoughts about code style and naming conventions. Now you have a not that long peace of code that has already messed you to say nothing about somebody seeing this first time. ;)

You code is so messed and bad styled that personally I would let it RIP and recode from scratch. Keeping in mind straight and flexible logic, helpful comments and meaningful naming.

Regards,
Fish

Thanks Fish for your suggestions.

I changed the following Access VBA code a little bit and it is working as I expected.

I agree that the following code is bad styled but haven't bothered much since it is working.

And regarding the naming conventions I wantedly changed the names because I don't want to reveal the actual names that I am using and I only took a part of the code which is not working and showed it to you to make it simple.

But as you said I should have come up with meaningful names and helpful comments to give you a clear idea.

Next time I will definitely come up with meaningful names.

Thank you once again for all your help.

1. Access VBA module:

Public eXL As New eventsXL
 
Function Create_ComboBoxes()
Dim OLEObj As OLEObject, OLEObj1 As OLEObject
Dim myRng As Range, myCell As Range, myRng1 As Range, myCell1 As Range
Dim cellAddr As String, curChar As String, actualAddr As String, X As Integer
 
With eXL
If .XL Is Nothing Then Set .XL = New Excel.Application
.XL.Visible = True
.XL.Interactive = True
Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
Set .WS = .WB.Worksheets("Example")
.WS.Activate
 
.XL.CommandBars("Control Toolbox").Visible = False
.WS.OLEObjects.Delete
 
Set myRng = .WS.Range("E2:E4")
For Each myCell In myRng.Cells
With myCell
    actualAddr = " "
    .NumberFormat = ";;;" 'hide the value in the cell
    Set OLEObj = .Parent.OLEObjects.Add _
                (ClassType:="Forms.ComboBox.1", Link:=False, _
                DisplayAsIcon:=False, _
                Top:=.Top, _
                Left:=.Left, _
                Width:=.Width, _
                Height:=.Height)
End With
 
cellAddr = myCell.Address
 
For X = 1 To Len(cellAddr)
    curChar = Mid(cellAddr, X, 1)
    If InStr("$", curChar) = 0 Then actualAddr = _
           actualAddr & curChar
Next X
.Abc OLEObj.Object, actualAddr
Next myCell
End With
End Function
 
Function Export_ExtractedPolicyList()
Dim excelFile As String, tableName As String
excelFile = "C:\Book1.xls"
tableName = "Example"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName, excelFile, True
End Function
 
Public Function ImportExcelSheet(comboValue As String, ceAddr As String)
eXL.XL.Range(ceAddr).Value = comboValue
End Function

2. "eventsXL" class module:

Public WithEvents XL As Excel.Application
Public WithEvents WB As Excel.Workbook
Public WithEvents WS As Excel.Worksheet
Private myObj As New VBA.Collection
 
Public Property Get myComboBoxes() As VBA.Collection
    Set myComboBoxes = myObj
End Property
 
Public Function Abc(obj As Variant, cAddr As String) As Class1
    Set Abc = New Class1
    Set Abc.myComboBox = obj
    Abc.cellAddress = cAddr
    Me.myComboBoxes.Add Abc
End Function
 
Private Sub XL_WorkbookBeforeClose(ByVal WB1 As Excel.Workbook, Cancel As Boolean)
With XL.Workbooks
XL.Quit
Set eXL.XL = Nothing
Set eXL.WB = Nothing
Set eXL.WS = Nothing
Set eXL = Nothing
End With
Exit Sub
End Sub

3. "Class1" class module:

Public WithEvents myOLE As MSForms.ComboBox
Public cellAddress As String
 
Public Property Get myComboBox() As MSForms.ComboBox
Set myComboBox = myOLE
End Property
 
Public Property Set myComboBox(ByRef myCombo As MSForms.ComboBox)
Set myOLE = myCombo
myOLE.AddItem "A"
myOLE.AddItem "B"
myOLE.AddItem "C"
myOLE.AddItem "D"
End Property
 
Public Sub myOLE_Change()
Dim Item As String
ImportExcelSheet myOLE.Text, cellAddress
End Sub