Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting cells from a value in a Listbox
Hi, I have a spreadsheet, "Enter Data", to which is added a list of
companies and corresponding values. I have a macro to convert them into the correct format and position on another spreadsheet, "Template". There is stil one manual process which requires some font changing and border formatting for one company in the list. I can record a macro to do this formatting but i would like the user to pick the company from a listbox which then formats the 13 cell ranges. I have tried to create the listbox on the spreadsheet and also on a form but I am unable to mange to populate the listbox with the company valuse. I have looked at the examples posted but I have not been able to get them to run on this spreadsheet. Can anyone help. Thanks in advance. Davie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting cells from a value in a Listbox
Dave
I did not bother with the formatting issue but filling the listbox is very simple. The attached code is executed from a button on the worksheet. The sheet contains a range "companies" that has the list of companies The userform just has a listbox called listbox1 here is the code Private Sub UserForm_Initialize() Dim co As Variant With ListBox1 For Each co In Range("companies") .AddItem co Next End With hope this helps End Sub "davethewelder" wrote: Hi, I have a spreadsheet, "Enter Data", to which is added a list of companies and corresponding values. I have a macro to convert them into the correct format and position on another spreadsheet, "Template". There is stil one manual process which requires some font changing and border formatting for one company in the list. I can record a macro to do this formatting but i would like the user to pick the company from a listbox which then formats the 13 cell ranges. I have tried to create the listbox on the spreadsheet and also on a form but I am unable to mange to populate the listbox with the company valuse. I have looked at the examples posted but I have not been able to get them to run on this spreadsheet. Can anyone help. Thanks in advance. Davie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting cells from a value in a Listbox
Dave
I did not realise u wanted the values associated with the companies in the listbox. Sorry about that See revised code below the "companies" range now covers 2 columns in the worksheet with the company names in the 1st and the "values" in the 2nd. the 1st company name is defined as name "companytop" the listbox is changed to 2 columns in the code... ******************* Private Sub UserForm_Initialize() Dim co As Variant Dim r As Variant Dim count As Long Range("companytop").Select For Each co In Range("companies") r = r + 1 Next With ListBox1 .ColumnCount = 2 For count = 1 To r .AddItem ActiveCell.Offset(0, 0) .Column(1, count - 1) = ActiveCell.Offset(0, 1) ActiveCell.Offset(1, 0).Select Next End With End Sub ******************* "davethewelder" wrote: Hi, I have a spreadsheet, "Enter Data", to which is added a list of companies and corresponding values. I have a macro to convert them into the correct format and position on another spreadsheet, "Template". There is stil one manual process which requires some font changing and border formatting for one company in the list. I can record a macro to do this formatting but i would like the user to pick the company from a listbox which then formats the 13 cell ranges. I have tried to create the listbox on the spreadsheet and also on a form but I am unable to mange to populate the listbox with the company valuse. I have looked at the examples posted but I have not been able to get them to run on this spreadsheet. Can anyone help. Thanks in advance. Davie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
listbox to cells?? | Excel Programming | |||
Formatting Listbox | Excel Discussion (Misc queries) | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Date formatting in a multi column listbox | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |