Link ActiveX to Excel

Question

Hope somebody can help!

I am programming in VB6 and am trying to write an activeX control for an indicator. The indicator should change color relative to an excel open workbook cell being true or false. The indicator should be auto updating i.e. the indicator needs to link live to the excel cell.

I can then place several of the indicators c/w links to different cells on a userform. The workbook is opened and tested in the userform and object references set up ok.

I can't figure out how to link the indicator to the excel cell.

This is part of a larger project I am trying. Other control such as bargraphs, Switches etc. to be added if I can get the first one working.

Thanks in advance

Are you sure you want to create NEW functionality using VB6? — GTG

Mark Bertenshaw Mark Bertenshaw · Accepted Answer · 2014-04-11T16:14:26

You need to first add a reference to the Microsoft Excel x.x library in your control's references.

Next, you should add a private module level variable of type Excel.Worksheet, and declare it WithEvents, e.g.

Private WithEvents m_oWorksheet As Excel.Worksheet

You should also create a Property Set procedure called Worksheet which sets this variable.

Then you should add code for its Change event, e.g.

Private Sub m_oWorksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Row = 2 Then ' m_oWorksheet.Range("A2")
        'Do some coding here
    End If
End Sub

Obviously, .Column = 1 and .Row = 2 would be replaced by the cell coordinates you are interested in. I originally used coordinates like "A2", but found that the objects returned from m_oWorksheet.Range("A2") cannot be directly compared with the Target object, e.g.

If Target Is m_oWorksheet.Range("A2") Then

I tried to extract the cell reference "A2" from Target, but I can't seem to find a way to do it, unless you write a function to do the conversion of Column/Row to a string reference.

Note the previous answer I provided was very wrong, since I was testing

If Target = m_oWorksheet.Range("A2) Then

... which only worked because the default value properties were identical. This would fall over if any changed cell had the same value as a "watched" cell.

Link ActiveX to Excel

1 Answers