Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL LIST
Hi, i have a column with is very narrow, about the length of a charactor, if
the user selects column B for example a list choice appears, but the list isn't wide enough to see each option, is they anyway you can automatically open the list to the correct size and once selected go back to its original size? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL LIST
Hi,
Right click your sheet tab, view code and paste the code below in. Note the commented out line. You can use autofit or fixed widths with this line. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 2 Then 'Target.ColumnWidth = 8.5 Columns(2).EntireColumn.AutoFit Else Columns(2).ColumnWidth = 2# End If End Sub Mike "Neil Holden" wrote: Hi, i have a column with is very narrow, about the length of a charactor, if the user selects column B for example a list choice appears, but the list isn't wide enough to see each option, is they anyway you can automatically open the list to the correct size and once selected go back to its original size? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL LIST
maybe you could use the sheet's selection change event -- rigth click th
etab, select view code and paste this: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 2 Then Columns(2).ColumnWidth = 20 Else Columns(2).ColumnWidth = 2 End If End Sub whenever you select any cell, the code call this procedure. If the selection is in B, the column with gets widened, otherwise it resets to the smaller width just an idea "Neil Holden" wrote: Hi, i have a column with is very narrow, about the length of a charactor, if the user selects column B for example a list choice appears, but the list isn't wide enough to see each option, is they anyway you can automatically open the list to the correct size and once selected go back to its original size? Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL LIST
Hi, thanks for your reply, but i need it to go back to its original column
width size after its selected? Thanks "Neil Holden" wrote: Hi, i have a column with is very narrow, about the length of a charactor, if the user selects column B for example a list choice appears, but the list isn't wide enough to see each option, is they anyway you can automatically open the list to the correct size and once selected go back to its original size? Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCEL LIST
unfortunately the selection chaneg fikres after the change, so we need a
flag...in the code below, the boolean bSkip is the flag - its set to true if a value changes in B and this prevents the column width expandin Option Explicit Private bSkip As Boolean Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then Columns(2).ColumnWidth = 2 bSkip = True End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If bSkip Then bSkip = False ' reset the flag and exit Exit Sub End If If Target.Column = 2 Then Columns(2).ColumnWidth = 20 Else Columns(2).ColumnWidth = 2 End If End Sub "Neil Holden" wrote: Hi, thanks for your reply, but i need it to go back to its original column width size after its selected? Thanks "Neil Holden" wrote: Hi, i have a column with is very narrow, about the length of a charactor, if the user selects column B for example a list choice appears, but the list isn't wide enough to see each option, is they anyway you can automatically open the list to the correct size and once selected go back to its original size? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003. Data/Validation/Settings - Allow/List: sizing list? | Excel Discussion (Misc queries) | |||
excel list of names, addresses and email to address book/contact list??? | Excel Discussion (Misc queries) | |||
How to Create a macro from drop down list (Validation List) in excel | Excel Programming | |||
Want to Create a List in Excel 2002; Don't see List in Data Menu? | Excel Discussion (Misc queries) | |||
My Excel drop-down list eliminates from list options chosen. Help | Excel Programming |