Formatting cells from a value in a Listbox

[davethewelder]

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

 

[LeShark]

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

 

[LeShark]

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

*******************