List box activeX control and linkedcell value

[Sirpent0r]

Hi,

I am creating a list box containing serveral characters from the wingdings
font set. I am using the activeX control version so I can set the font that
is displayed in the list box to display correctly. So it displays an arrow
symbol instead of the ASCII equivelent "é".

I would then like to have this single listbox containg these items be
applied to a range of cells instead of just one cell as defined by the
LinkedCell setting. I tried using a range in this field, but it only accepts
a single cell (for example A1). I would like to be able to have this apply to
A1:A10.

Kind of like when the user clicks on the appropriate cell, say A2, the
linkedCell value changes to A2 and the appropriate symbol can be selected
from the drop down list. If the user selects A3, same thing.


thanks

 

[Dave Peterson]

Maybe you can rely on a macro to assign the linked cell to the activecell
(whereever that is!) when you click on the listbox with the mouse????

If you want to try, rightclick on the worksheet tab that contains the listbox
and select view code. Paste this into the newly opened code window:

Option Explicit
Private Sub ListBox1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Me.ListBox1.LinkedCell = ActiveCell.Address(external:=True)

End Sub

Then back to excel to test it out.

I'm not sure what you're doing, but if you wanted to make sure you were in a
cell that should be linked, you could even check first:

Option Explicit
Private Sub ListBox1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

If Intersect(ActiveCell, Me.Range("b1:b10")) Is Nothing Then
'remove the linked cell
Me.ListBox1.LinkedCell = ""
Else
Me.ListBox1.LinkedCell = ActiveCell.Address(external:=True)
End If
End Sub

 

[Sirpent0r]

Thanks Dave,

That seems to work great!

Maybe you can rely on a macro to assign the linked cell to the activecell
(whereever that is!) when you click on the listbox with the mouse????

If you want to try, rightclick on the worksheet tab that contains the listbox
and select view code. Paste this into the newly opened code window:

Option Explicit
Private Sub ListBox1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Me.ListBox1.LinkedCell = ActiveCell.Address(external:=True)

End Sub

Then back to excel to test it out.

I'm not sure what you're doing, but if you wanted to make sure you were in a
cell that should be linked, you could even check first:

Option Explicit
Private Sub ListBox1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

If Intersect(ActiveCell, Me.Range("b1:b10")) Is Nothing Then
'remove the linked cell
Me.ListBox1.LinkedCell = ""
Else
Me.ListBox1.LinkedCell = ActiveCell.Address(external:=True)
End If
End Sub