Hello everyone,
I have added a ComboBox in excel spreadsheet using the following Access VBA code. - Public eXL As New eventsXL
-
-
Function Create_ComboBox()
-
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
-
Set OLEObj = .WS.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
-
DisplayAsIcon:=False, Left:=162, Top:=32.25, Width:=110.25, Height:= _
-
33.75).Select
-
-
Set myOLEObj = OLEObj.Object
-
With myOLEObj
-
.AddItem "A"
-
.AddItem "B"
-
.AddItem "C"
-
.AddItem "D"
-
End With
-
End With
-
End Function
And, now I would like to display 'MsgBox' whenever a user selects a value from the drop down list of the above created combo box. But some how I am unable to do this!
I created "eventsXL" file and defined the following ComboBox event in order to handle the events. - Public WithEvents XL As Excel.Application
-
Public WithEvents WB As Excel.Workbook
-
Public WithEvents WS As Excel.Worksheet
-
Public WithEvents ComboBox1 As ComboBox
-
-
Private Sub ComboBox1_click()
-
MsgBox "Hello"
-
End Sub
Any suggestions/ideas will be greatly appreciated. Thanks in advance!
26  20182  FishVal 2,653
Recognized Expert Specialist
Hi there.
Two points. - Where do you instantiate Combobox1?
- When declaring variable of class Combobox its a good idea to use explicit library reference.
Kind regards,
Fish
Hi there.
Two points.- Where do you instantiate Combobox1?
- When declaring variable of class Combobox its a good idea to use explicit library reference.
Kind regards,
Fish
Hello FishVal, thanks for the reply. I changed the code a little bit as follows:
1. Created the following Access VBA module - Public eXL As New eventsXL
-
-
Function Create_ComboBox()
-
Dim OLEObj As OLEObject
-
Dim myOLEObj As MSForms.ComboBox
-
-
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("SelectPolicies")
-
.WS.Activate
-
-
.XL.CommandBars("Control Toolbox").Visible = False
-
.WS.OLEObjects.Delete
-
Set OLEObj = .WS.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
-
DisplayAsIcon:=False, Left:=266, Top:=14, Width:=110.25, Height:= _
-
23.75)
-
-
Set myOLEObj = OLEObj.Object
-
With myOLEObj
-
.AddItem "A"
-
.AddItem "B"
-
.AddItem "C"
-
.AddItem "D"
-
End With
-
End With
-
End Function
2. Created "eventsXL" class module as follows: - Public WithEvents XL As Excel.Application
-
Public WithEvents WB As Excel.Workbook
-
Public WithEvents WS As Excel.Worksheet
-
Public WithEvents ComboBox1 As ComboBox
-
-
Private Sub ComboBox1_click()
-
MsgBox "Hello"
-
End Sub
-
-
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
And regarding your questions,
a) I created a combo box "ComboBox1" using line #17 (first step code), this is where I instantiated the ComboBox1.
b) I am not sure how to declare variable of class Combobox using explicit library reference.
Could you please let me know how to deal with the problem?
Thank you very much!
FishVal 2,653
Recognized Expert Specialist
I still don't see there an instruction like
Set eXL.Combobox1=m yOLEobj
There are also multiple code lines in "eventsXL" class module which will not work as you are expecting.
Kind regards,
Fish
I still don't see there an instruction like
Set eXL.Combobox1=m yOLEobj
There are also multiple code lines in "eventsXL" class module which will not work as you are expecting.
Kind regards,
Fish
Thanks for your wonderful suggestion. I changed the code to the following as per your suggestion. And now I am able call "Change" event whenever I click a value in the dropdown list of the dynamically created combo box.
But I ran into another problem. Hope you can help me on this!
This is what I am doing:
1. Created the following Access VBA module, which open an excel file and creates combo boxes in the following given range of cells (line #24) - Public eXL As New eventsXL
-
-
Function Create_ComboBox()
-
Dim OLEObj As OLEObject
-
Dim myRng As Range, myCell As Range
-
Dim LastUsedRow As Long
-
-
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
-
-
If .XL.WorksheetFunction.CountA(.XL.Cells) > 0 Then
-
LastUsedRow = .WS.Cells.Find(what:="*", after:=.WS.Cells(1, 1), lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
-
'MsgBox " " & LastUsedRow
-
End If
-
-
.XL.CommandBars("Control Toolbox").Visible = False
-
.WS.OLEObjects.Delete
-
-
Set myRng = .WS.Range("E2:E" & LastUsedRow)
-
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
-
Set .myOLEObj = OLEObj.Object
-
With .myOLEObj
-
.AddItem "A"
-
.AddItem "B"
-
.AddItem "C"
-
.AddItem "D"
-
End With
-
Next myCell
-
End With
-
End Function
2. Created the following class module to define combo box events - Option Compare Database
-
-
Public WithEvents XL As Excel.Application
-
Public WithEvents WB As Excel.Workbook
-
Public WithEvents WS As Excel.Worksheet
-
Public WithEvents myOLEObj As MSForms.ComboBox
-
-
Private Sub myOLEObj_Change()
-
MsgBox "Hello"
-
End Sub
-
-
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
The above code is working fine only for the last created combo box, but if I click any of the combo boxes except the last one the code is not displaying "MsgBox" (i.e., not calling "Change" event).
Is there any way that I can assign combo box variable (myOLEObj) to an array variable and attach WithEvents to all created combo boxes in the spreadsheet.
Hope I did not confused you!
I really appreciate your help on this. Thanks!
FishVal 2,653
Recognized Expert Specialist
Access help says array variable could not be declared WithEvents.
Though you may create an additional class where you instantiate MSForms.Combobo x variable and put event handling code, then you declare array or collection of this class in "eventXL" class and fill it with objects of this class. MSForms.Combobo x events will be handled in these multiple instances of this class.
Regards,
Fish
Access help says array variable could not be declared WithEvents.
Though you may create an additional class where you instantiate MSForms.Combobo x variable and put event handling code, then you declare array or collection of this class in "eventXL" class and fill it with objects of this class. MSForms.Combobo x events will be handled in these multiple instances of this class.
Regards,
Fish
Hello FishVal, I did not exactly follow the idea you stated above.
Could you please give me an example on your above approach? Thank you very much!
FishVal 2,653
Recognized Expert Specialist
Hello FishVal, I did not exactly follow the idea you stated above.
Could you please give me an example on your above approach? Thank you very much!
Ok.
I'm going to attach simple mdb (useless as is :)) demonstrating a method of creating collection of objects reusing the same event handler.
In the example Form1 has 20 buttons added to collection on load. Each button is added within wrapping class "ButtonWrap per" to a collection within "ButtonsCollect ion" class which is being instantiated as global object variable in Form1 module.
Kind regards,
Fish
Ok.
I'm going to attach simple mdb (useless as is :)) demonstrating a method of creating collection of objects reusing the same event handler.
In the example Form1 has 20 buttons added to collection on load. Each button is added within wrapping class "ButtonWrap per" to a collection within "ButtonsCollect ion" class which is being instantiated as global object variable in Form1 module.
Kind regards,
Fish
Hello Fish, thank you very much for the example.
I am pulling my hair to solve this problem. I came up with the following code with the help of your example.
But I am clueless now. Please help me on how to proceed further.
1. Created the following Access VBA module to open an excel file, add combo boxes in the given following range of cells and add items to the combo boxes.
(I set the created combo box to "Abc" class defined in "eventsXL" class module to handle events (line #43)) - Public eXL As New eventsXL
-
-
Function Create_ComboBox()
-
Dim OLEObj As OLEObject
-
Dim myRng As Range, myCell As Range
-
Dim LastUsedRow As Long
-
-
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
-
-
If .XL.WorksheetFunction.CountA(.XL.Cells) > 0 Then
-
LastUsedRow = .WS.Cells.Find(what:="*", after:=.WS.Cells(1, 1), lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
-
'MsgBox " " & LastUsedRow
-
End If
-
-
.XL.CommandBars("Control Toolbox").Visible = False
-
.WS.OLEObjects.Delete
-
-
Set myRng = .WS.Range("E2:E" & LastUsedRow)
-
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
-
Set .myOLEObj = OLEObj.Object
-
With .myOLEObj
-
.AddItem "A"
-
.AddItem "B"
-
.AddItem "C"
-
.AddItem "D"
-
End With
-
Set .myOLEObj = .Abc
-
Next myCell
-
End With
-
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
-
Public myOLEObj As msforms.ComboBox
-
-
Public Property Get myComboBoxes() As msforms.ComboBox
-
Set myComboBoxes = myOLEObj
-
End Property
-
-
Public Function Abc() As Class1
-
Set Abc = New Class1
-
Set Abc.myComboBox = myComboBoxes
-
End Function
-
3. Created the following "Class1" class module to handle combo box events - Public WithEvents myOLE As msforms.ComboBox
-
-
Public Property Get myComboBox() As msforms.ComboBox
-
Set myComboBox = myOLE
-
End Property
-
-
Public Property Set myComboBox(myCombo As msforms.ComboBox)
-
Set myOLE = myCombo
-
End Property
-
-
Private Sub myOLE_Change()
-
MsgBox "Hello"
-
End Sub
I am getting "Type Mismatch" error at line #8 in "Class1" class module.
Your help on this will be greatly appreciated. Thanks for your valuable time.
FishVal 2,653
Recognized Expert Specialist
You try to assign Class1 object to eventsXL.myOLEo bj variable declared as MSForms.Combobo x.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Tammy |
last post by:
I am doing some genealogy research and I have discovered that there is
a lot of data available on the web in text format. The problem is
that the columns in the files are lined up by spaces. I'd like to be
able to import these files into Access or Excel so that they can be
sorted.
What I plan on doing is adding in commas at the end of each field to
create a csv file. What I need to know is how can I easily remove the
spaces between...
|
by: D. Shane Fowlkes |
last post by:
This most likely belongs in another forum but I thought I'd start here. I
have a COM Object written in VB6. The DLL will access MS Excel and use it's
Object Library to write a customized report and saves it to a folder. The
DLL even writes to a log for each step it takes so we can troubleshoot the
problems (if any).
This works fine on one machine but not another. The folder the DLL is trying
to write to has full permissions assigned...
|
by: SKarnis |
last post by:
We are trying to rebuild a current Access 2002 (XP) mdb program to VB.NET
with a SQL database - we are having problems with a suitable combobox.
There are many threads discussing multiple column comboboxes in .NET. We
are having success with the multiple columns similar to the combobox from
Access 2002 (XP).
Our biggest problem is speed. In a form in Access 2002, our combobox is
able to load data in a table in separate Access database...
|
by: Jordi |
last post by:
Hello,
can anyone tell what is the best way to access to a excel spread sheet from
c++?
I would be interested in read it and write it.
Thank you in advance
Jordi
|
by: garry.oxnard |
last post by:
Can anyone help me to solve a problem which involves switching from
Access to Excel (then back to Access) programatically please?
I have an Excel template which, on open, also opens an Access database
- containing a list of addresses.
Sequence of events is =
(1) Excel template opens in its default XXX.xls filename.
(2) Code runs to save the spreadsheet as XXX.xls.
(3) User clicks a button to open an Access database containing an
| | |
by: aniphilip |
last post by:
Hi All,
I have a requirement to automate Access functionality from Excel. I need to open Access, trigger button events and close Access from Excel.
I Achieved opening Access from Excel. I cannot find a way to trigger a button click event from Excel for an Access form.
To be more precise,
I have Access db called a.mdb. a.mdb has a.form and a.command button inside the form
I have xls sheet called b.xls. How I will fire an onclick event...
|
by: torontolancer |
last post by:
Hi there how r u .I would really appriciate ur concern regarding checking out this code.
its beind a command button and i am have a combo box selecing a query criteria and then pressing the button to take care recoredset from access to excel.
i have checked the code in any ways its working fine but when i use the combo box criteria
forms!formname!combobox
in the query the code breaks down at
rst.open a """""""""""gives...
|
by: peteh |
last post by:
Hi All;
I have tried to find this info on the IBM site, but alas... We are
running DB2 v9.1.1 and making extensive use of Information Integrator
(now known as Websphere Federation Server?!) to access Oracle and
other DB2s (LUW, AS400 and z/OS).
We have some requirements to integrate Excel data. In looking at our
options 18 months ago (v8.2), we needed some additional Windows
components (drivers and ODBC communication software) to make...
|
by: Simon |
last post by:
Dear reader,
How to export a hyperlink field from Access to Excel, so it is also working
in Excel.
In Excel you have for the Hyperlink to parts:
a.. Text To Display
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
