Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Events to Shrink and Expand Width
This is something that is a thorn in my side that I can't figure out. It's
probably not worth the time I have put into it, but that is programming I guess. I have a combobox that contains a list of 2 columns. Column 1 is displayed in the textbox portion of the combobox and it contains two letter intials that represent what is in Column 2, which is the description. For example, ' Product Code Description Column 1 Column 2 PF Plastic Face CC Custom Cabinet PT Parts Column 1 is the Product Code and shows in the textbox portion. Column 2 shows in the drop down list. The combobox needs to have an original width = 53, but I need to expand it to 120 if the user wants to change the product code so they can see the full description. This is what I have but the combobox doesn't go back to the origianl size of 53 after the combobox has been changed. Anyone have any ideas? This what I have: Private Sub cboProductCode_Change() ' put combobox to it original width cboProductCode.Width = 53 End Sub Private Sub cboProductCode_DropButtonClick() ' expand combobox to view list cboProductCode.Width = 120 End Sub -- Cheers, Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Events to Shrink and Expand Width
I think you have stumbled across an anomaly. I tried this code.
Private Sub cboProductCode_Change() ' put combobox to it original width cboProductCode.ColumnCount = 1 's = Timer + 2 'Do While Timer < s ' DoEvents 'Loop If cboProductCode.ColumnCount = 1 Then cboProductCode.Width = 53 End If End Sub Private Sub cboProductCode_DropButtonClick() ' expand combobox to view list cboProductCode.ColumnCount = 2 cboProductCode.Width = 120 End Sub When running the code with the comment marks removed, it changes the size of the combobox OK, but as soon as the dropdown arrow is clicked, it adds the second column back in and expands the size. Yet, I can manually walk through the code to reduce the size back to one column and 53 pts and it allows me to drop down with only one column visible. I threw in the delay to see if that would give the same effect as doing it manually, but no luck. Weird! Sorry Ryan, guess I can't help. "RyanH" wrote: This is something that is a thorn in my side that I can't figure out. It's probably not worth the time I have put into it, but that is programming I guess. I have a combobox that contains a list of 2 columns. Column 1 is displayed in the textbox portion of the combobox and it contains two letter intials that represent what is in Column 2, which is the description. For example, ' Product Code Description Column 1 Column 2 PF Plastic Face CC Custom Cabinet PT Parts Column 1 is the Product Code and shows in the textbox portion. Column 2 shows in the drop down list. The combobox needs to have an original width = 53, but I need to expand it to 120 if the user wants to change the product code so they can see the full description. This is what I have but the combobox doesn't go back to the origianl size of 53 after the combobox has been changed. Anyone have any ideas? This what I have: Private Sub cboProductCode_Change() ' put combobox to it original width cboProductCode.Width = 53 End Sub Private Sub cboProductCode_DropButtonClick() ' expand combobox to view list cboProductCode.Width = 120 End Sub -- Cheers, Ryan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Events to Shrink and Expand Width
The combobox doesn't like to resize while it is in the state of dropdown.
You could call an OnTime macro to change it but why not do it in the Exit event. That would also resize it even if user does not change a value in which scenario the change event would not even fire. Regards, Peter T "RyanH" wrote in message ... This is something that is a thorn in my side that I can't figure out. It's probably not worth the time I have put into it, but that is programming I guess. I have a combobox that contains a list of 2 columns. Column 1 is displayed in the textbox portion of the combobox and it contains two letter intials that represent what is in Column 2, which is the description. For example, ' Product Code Description Column 1 Column 2 PF Plastic Face CC Custom Cabinet PT Parts Column 1 is the Product Code and shows in the textbox portion. Column 2 shows in the drop down list. The combobox needs to have an original width = 53, but I need to expand it to 120 if the user wants to change the product code so they can see the full description. This is what I have but the combobox doesn't go back to the origianl size of 53 after the combobox has been changed. Anyone have any ideas? This what I have: Private Sub cboProductCode_Change() ' put combobox to it original width cboProductCode.Width = 53 End Sub Private Sub cboProductCode_DropButtonClick() ' expand combobox to view list cboProductCode.Width = 120 End Sub -- Cheers, Ryan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Events to Shrink and Expand Width
I read the help on DoEvents and don't really understand it. What are some
situations you would need to use DoEvents and what does it do exactly? -- Cheers, Ryan "JLGWhiz" wrote: I think you have stumbled across an anomaly. I tried this code. Private Sub cboProductCode_Change() ' put combobox to it original width cboProductCode.ColumnCount = 1 's = Timer + 2 'Do While Timer < s ' DoEvents 'Loop If cboProductCode.ColumnCount = 1 Then cboProductCode.Width = 53 End If End Sub Private Sub cboProductCode_DropButtonClick() ' expand combobox to view list cboProductCode.ColumnCount = 2 cboProductCode.Width = 120 End Sub When running the code with the comment marks removed, it changes the size of the combobox OK, but as soon as the dropdown arrow is clicked, it adds the second column back in and expands the size. Yet, I can manually walk through the code to reduce the size back to one column and 53 pts and it allows me to drop down with only one column visible. I threw in the delay to see if that would give the same effect as doing it manually, but no luck. Weird! Sorry Ryan, guess I can't help. "RyanH" wrote: This is something that is a thorn in my side that I can't figure out. It's probably not worth the time I have put into it, but that is programming I guess. I have a combobox that contains a list of 2 columns. Column 1 is displayed in the textbox portion of the combobox and it contains two letter intials that represent what is in Column 2, which is the description. For example, ' Product Code Description Column 1 Column 2 PF Plastic Face CC Custom Cabinet PT Parts Column 1 is the Product Code and shows in the textbox portion. Column 2 shows in the drop down list. The combobox needs to have an original width = 53, but I need to expand it to 120 if the user wants to change the product code so they can see the full description. This is what I have but the combobox doesn't go back to the origianl size of 53 after the combobox has been changed. Anyone have any ideas? This what I have: Private Sub cboProductCode_Change() ' put combobox to it original width cboProductCode.Width = 53 End Sub Private Sub cboProductCode_DropButtonClick() ' expand combobox to view list cboProductCode.Width = 120 End Sub -- Cheers, Ryan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox Events to Shrink and Expand Width
I have used the Exit Event before, but the control has to loose focus before
it fires. I guess I will have to live with the combo box expanded until the use click somewhere else. -- Cheers, Ryan "Peter T" wrote: The combobox doesn't like to resize while it is in the state of dropdown. You could call an OnTime macro to change it but why not do it in the Exit event. That would also resize it even if user does not change a value in which scenario the change event would not even fire. Regards, Peter T "RyanH" wrote in message ... This is something that is a thorn in my side that I can't figure out. It's probably not worth the time I have put into it, but that is programming I guess. I have a combobox that contains a list of 2 columns. Column 1 is displayed in the textbox portion of the combobox and it contains two letter intials that represent what is in Column 2, which is the description. For example, ' Product Code Description Column 1 Column 2 PF Plastic Face CC Custom Cabinet PT Parts Column 1 is the Product Code and shows in the textbox portion. Column 2 shows in the drop down list. The combobox needs to have an original width = 53, but I need to expand it to 120 if the user wants to change the product code so they can see the full description. This is what I have but the combobox doesn't go back to the origianl size of 53 after the combobox has been changed. Anyone have any ideas? This what I have: Private Sub cboProductCode_Change() ' put combobox to it original width cboProductCode.Width = 53 End Sub Private Sub cboProductCode_DropButtonClick() ' expand combobox to view list cboProductCode.Width = 120 End Sub -- Cheers, Ryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I expand & shrink columns? | Excel Discussion (Misc queries) | |||
How to set a cell to expand to fit text in Excel not shrink fit | Excel Worksheet Functions | |||
Header Row with Option to Shrink/Expand Rows Beneath | Excel Worksheet Functions | |||
shrink to fit expand | Excel Programming | |||
Shrink and expand a number of columns/rows | Excel Worksheet Functions |