PDA

View Full Version : Please help with VBA (Excel 2003)... ImageCombo Box Bug???



NateDiggity
09-09-2008, 04:15 PM
Hi All,

I am trying to write a VBA script in Excel to automate some data analysis. I am familiar with VB and VBA (not an expert but seasoned). I can't get the ImageCombo box to work properly with a multipage object.

I've narrowed the problem down to the "visibile" property of the ImageCombo box... You can not populate the ImageCombo box when it is not visible (visible set to false or on an inactive multipage page)... Additionally, if you do populate it when it is visible then make it invisible (set property to false or change multipage page) the data in the box disappears! However, it still seems to be populated as the ImageCombo1.ComboItems.Count value still equals the number of items in it.

I have looked everwhere I could find on the net to see if it's something I am doing wrong or if it is a known problem... I couldn't find anyone who described the same/similar problem.

Attached is a sheet that contains a form/code that shows what I am describing. Has anyone run into a similar problem? Anyone know of a solution? Any help would be appreciated. Thanks in advance!

NateDiggity
09-10-2008, 07:30 AM
I noticed a several people have looked at this thread but only a few at the code... In case anyone is worried, the xls above does not contain any malicious code. The code it contains is (and an image of the form is attached)... All objects have their default names. If anyone can help me out I'd really appreciate it!

Private Sub CommandButton1_Click()
'Set visible to false if true or true if false
If ImageCombo1.Visible = True Then
ImageCombo1.Visible = False
Else
ImageCombo1.Visible = True
End If
End Sub
Private Sub UserForm_Initialize()

'Initialize ImageCombo1
ImageCombo1.ImageList = ImageList1
ImageCombo1.ComboItems.Add Index:=1, Text:="Image1", Image:="Image1"
End Sub

Paul_Hossler
09-10-2008, 11:39 AM
Try using the Multipage_Change event to set and re-set things when you change tabs. I believe .Index (0 base) will tell you the current tab



Private Sub CommandButton1_Click()
'Set visible to false if true or true if false
ImageCombo1.Visible = Not ImageCombo1.Visible
End Sub
Private Sub MultiPage1_Change()

End Sub
Private Sub UserForm_Initialize()

'Initialize ImageCombo1
ImageCombo1.ImageList = ImageList1
ImageCombo1.ComboItems.Add Index:=1, Text:="Image1", Image:="Image1"
End Sub


Paul

NateDiggity
09-11-2008, 09:04 PM
Hi Paul,

Thanks for the reply. By reset things I'm guessing you are talking about refreshing the imagecombo... I should have mentioned before that I tried refreshing the imagecombo with no luck. I even went through every event/property I could find for the imagecombo that seemed like it might have been appropriate and couldn't find anything that fixed the problem.

I guess I could save the selection in the imagecombo when the page is changed then when the page is changed back dump the old values, reload in new values (same ones just repopulated), and select the previously selected value. I really dislike bandaid "fixes". I'd really prefer to do it right.

Any recommendations? Or is this problem a bug that can't be properly fixed?


Try using the Multipage_Change event to set and re-set things when you change tabs. I believe .Index (0 base) will tell you the current tab



Private Sub CommandButton1_Click()
'Set visible to false if true or true if false
ImageCombo1.Visible = Not ImageCombo1.Visible
End Sub
Private Sub MultiPage1_Change()

End Sub
Private Sub UserForm_Initialize()

'Initialize ImageCombo1
ImageCombo1.ImageList = ImageList1
ImageCombo1.ComboItems.Add Index:=1, Text:="Image1", Image:="Image1"
End Sub


Paul

NateDiggity
09-11-2008, 09:07 PM
BTW, thanks for the more elegant code...

ImageCombo1.Visible = Not ImageCombo1.Visible

That's nice!

Paul_Hossler
09-13-2008, 07:17 AM
I'm not familiar with ImageCombo, etc, but I have used Multipage

I've just used something simple like this:



Private Sub MultiPage1_Change()
MsgBox "Now on Page" & (MultiPage1.Value + 1) & " Configure as needed before display"
End Sub



FWIW, I don't understand why you wanted the Command button to toggle visibility. A long shot: You said "I can't get the ImageCombo box to work properly with a multipage object. " - If you want it on two tabs, you have to put it in the twice, once on each tab. That give you 2, and each has to be programmed seperately


I just used a little driver sub to swith between Page 1 and 2 to verify that the MultiPage change event kicks in as I expected


Option Explicit

Sub test()
Load UserForm1
UserForm1.Show
End Sub


Paul

NateDiggity
09-13-2008, 08:25 AM
Hi Paul,

Thanks for the reply.... But I think we are talking about different problems. The problem I have is that an imagecombo on a page of a multipage goes blank when you switch from the page it is on to another page then back again. It also goes blank when you switch visibility to false then back to true... Additionally, I can't populate with code an imagecombo on a page that is not on the default multipage at start up. I suspect the multipage object uses the visible property in it's code to hide and show items on different pages when you click them... Thereby producing the same result as manually setting the visibility property to false and back to true.

On the real form I am working on I have multiple imagecombo boxes on multiple multipage pages (they are programmed seperately)... But like I said the go blank when I switch between pages and back again and I can't populate with code the ones that are not visible at startup.

If you would, please download the sheet in the first post, go into the VBA environment and run the code. The form will come up and have one item in the imagecombo. Then switch to the second page and back to the first... Now that item is gone! Same thing will happen if you press the commandbutton. If you insert "msgbox(Imagecombo1.ComboItems.count)" at the end of the commandbutton1 code you will find that the imagecombo still contains the one item but it is not being displayed. I've tried refreshing the imagecombo as well as every other thing I could think of. I've spent hours searching online to find the fix or if it's a known bug and couldn't find anything. This seems like such a standard type of thing (putting an imagecombo on a multipage) that either it would be known or not a problem at all. BTW, i've tried this on multiple computers with the same results.

Like I said before, I could program it so that before switching pages it stores your imagecombo selection then when you switch back clear the imagecombo, repopulate it, and reselect your previous selection. But it doesn't seem like I should have to do that (not SOP for a professionally available activeX component). Hopefully, I'm doing something stupid and this has an easy fix. Please take a look at the form and let me know what you think.

Paul_Hossler
09-13-2008, 12:32 PM
I'm famous for talking about diffrent problems :whistle:

Q: why are you setting the vibility property to false?


Paul

NateDiggity
09-13-2008, 08:12 PM
LOL... I hear ya. Me too.

In the real code/form I don't set the imagecombo visibility to false at anytime... what I did a bad job of explaining is that I think the multipage object activeX code sets the imagecombo visibility to false when you switch between pages. So, setting the imagecombo visibility to false/true with the command button has the same effect as changing pages (depopulates the imagecombo).


I'm famous for talking about diffrent problems :whistle:

Q: why are you setting the vibility property to false?


Paul

Paul_Hossler
09-14-2008, 07:51 AM
Well, this has been fun -- I learned some new things

Found this, and it had a WB that had images, etc. that I could fool with, aded a multi page control to the form, and some other stuff.

http://puremis.net/excel/code/069.shtm


The Multipage control does seem to make the ImageCombo forget. Only kudge I could come up with is to re-load the ImageCombo when you change to the page that it's on.

Maybe someone else has a more elegant approach

Paul


Option Explicit
Private Sub MultiPage1_Change()
If MultiPage1.Value = 0 Then ' first page ('Page 1') = 0
Call LoadImageCombo
End If
End Sub
Private Sub UserForm_Initialize()

Call LoadImageCombo

End Sub

Private Sub LoadImageCombo()
Dim aObjName As Variant
Dim aText As Variant
Dim imgLst As New ImageList
Dim i As Long
aObjName = Sheet1.Range("B2:B9").Value
aText = Sheet1.Range("C2:C9").Value
For i = 1 To 8
imgLst.ListImages.Add _
Key:="img" & i, _
Picture:=Sheet1.OLEObjects(aObjName(i, 1)).Object.Picture
'You can use LoadPicture for setting the Picture property
Next
Set ImageCombo1.ImageList = imgLst
For i = 1 To 7
ImageCombo1.ComboItems.Add _
Index:=i, _
Text:=aText(i, 1), _
Image:="img" & i, _
SelImage:="img8"
Next
Set imgLst = Nothing
End Sub