2

We are using a 3rd-party ActiveX grid control 10Tec iGrid in an Excel 2016 VBA application on a UserForm. We need to retrieve this control by the string name generated dynamically and cast it to its native type for further operations.

This is very easy to do for intrinsic MSForms types like CommandButton, for example:

Dim cmdbtn As iGrid
Set cmdbtn = Me.Controls("cmdOk")

However, this approach does not work for iGrid:

Dim grid As iGrid
Set grid = Me.Controls("iGrid1")

We always get the

Type mismatch

error.

This also does not help (the same problem):

Dim grid As iGrid
Set grid = Me.Controls("iGrid1").Object

Is there a way to get an ActiveX control in its native type dynamically by its string name in VBA UserForms?

braX
  • 11,506
  • 5
  • 20
  • 33
TecMan
  • 2,743
  • 2
  • 30
  • 64
  • Does `Set grid = Me.iGrid1` have the same problem? – Rory Nov 16 '17 at 15:21
  • @Rory, This is strange, but I have the same Type mismatch problem even for this statement. What can be the source of this problem? – TecMan Nov 16 '17 at 15:24
  • 1
    I'm not sure, but the only VBA snippet in their own online library simply uses `Control` as the type. – Rory Nov 16 '17 at 15:25
  • 1
    If you want to set a control to a specific type for the inbuilt types you usually refer to the library that holds them: Dim MyBox as MsForms.TextBox Do you need to do that for your iGrid? – Harassed Dad Nov 16 '17 at 17:01
  • @HarassedDad, there is no difference even if I fully qualify the type like `Dim grid As iGrid650_10Tec.iGrid` - because VBA can identify this iGrid type without any collisions with other libraries. Needless to mention that the link to the type library containing the iGrid types is also checked in the Tools\References dialog in VBA. – TecMan Nov 17 '17 at 05:55
  • It works if you define grid as type object correct? – KacireeSoftware Nov 17 '17 at 06:06
  • @KacireeSoftware, sorry, didn't understand. Can you explain what you meant? – TecMan Nov 17 '17 at 08:06
  • @tecman sorry man im at the deer lease. What i mean is if you declare the variable as Object does the code complain? 'Dim Grid as Object' 'Set Grid = Controls("iGrid1")' – KacireeSoftware Nov 18 '17 at 21:35
  • @KacireeSoftware, we need the native iGrid type to attach event handlers dynamically like in SWa's answer in [this](https://stackoverflow.com/questions/10592641/assign-event-handlers-to-controls-on-user-form-created-dynamically-in-vba) solution. – TecMan Nov 20 '17 at 08:12

1 Answers1

0

Try this:

Public iGridData As Control
Set iGridData = UserForm1.Controls.Add("iGrid700_10Tec.iGrid", "iGridName", True)
Vaibhav Vishal
  • 6,576
  • 7
  • 27
  • 48
Reza
  • 13
  • 6