how to make a Form Listbox linkedcell to present as text instead of number?

[feipezi]

Hello guys,
A quick question. Haven't created Excel macros for a while. I guess I did it before but unable to find a solution.
I know for ActiveX, this is not a problem. But for Form, it is number by default.

One more question.
The reason why I use Form to insert shapes, instead of ActiveX, is that in 2010 version of Excel, those inserted objects through ActiveX are not stable in a workbook. They will expand or shrink after clicking a particular object, like Combobox, Listbox and so on. Has the same thing happened to you guys? One of my associates told me it won't happen if it's Form object. Now I have 2013 Excel. Not sure if the bug, I think it's a bug, got fixed or not.

Thanks in advance.

 

[SkipVought]

Please post a specific example demonstrating your issue.

My immediate thought is to format the linked cell as text, BEFORE any value is assigned.

You do know that for Form Listbox control, the linked cell is an index counter, such the when the third list item is selected, the linked cell displays 3.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

No way to have text from the list in linked cell, use formula =INDEX(ListRange, LinkedCell) in other cell instead.

combo

 

[feipezi]

Thanks guys. I agree with you 100%. Here is the code I found. It was from some assignment I did before. Index() is definitely indispensable.
In fact, what about my 2nd question. Any input to share with me? It's bothered me for a long time.
Thanks again.


Sub listmanu()
Sheet8.Activate
With ActiveSheet.Shapes("List Box 2").OLEFormat.Object
For i = 1 To Range("program_name").Count
If .Selected(i) Then
multilist = multilist & "'" & Application.Index(Range("program_name"), i, 1) & "',"
' MsgBox multilist
End If
Next
multilist = Left(multilist, Len(multilist) - 1)
Sheet3.Cells(1, "ad") = multilist
End With
End Sub

Sub callmanu()
On Error Resume Next
Call listmanu
Sheet8.Activate
End Sub

 

[SkipVought]

I've had issues in the past with controls changing position and size. Consequently, I've made a practice of creating a procedure to position and size each control after a worksheet_change event and I use very few controls.

I've used ONE hidden combobox control with context-sensitive worksheet_change event for an in-cell selection multi-use drop down. That one also gets repositioned to the active cell, resized to the cell height and width and made visible with each relevant selection.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

So you have multiple selection list, slightly shorter:
[pre]With ActiveSheet.Shapes("List Box 2").OLEFormat.Object
For i = 1 To .ListCount
If .Selected(i) Then multilist = multilist & .List(i) & & "',"
Next i
End With
multilist = Left(multilist, Len(multilist) - 1)[/pre]

combo

 

[feipezi]

Thank you both for your input. I guess the instability of controls is a common issue. Even Excel 2013 has not solved the problems. I'll try to rely more on Form instead of ActiveX objects.

I need you guys to help me with some ADO messages. I have a snapshot attached. I do not understand what's wrong with the name of the table. I ran a few procedures with very similar table name without any issues until now.

you can see the SQL statement in the query cannot be simpler than this:



Select distinct mkt,product from [mktprod$] where mkt in ('BAB')


Thanks in advance.

 

[SkipVought]

Please put the new question in a new thread, where you can explain...
[tt] mkt,product[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein