Help With Userform Code

[glerwell]

Hi

I have a userform code, I was wondering if it could be streamlined and also if a value in a cell that is used is updated or changed then the userform updates automatically without having to close and rerun the macro to activate the userform to see the updates/changes.

Thanks

Here's the code:

Private Sub UserForm_Initialize()
Label4.Caption = Worksheets("SYSTEMS").Range("AN10").Value
Label5.Caption = Worksheets("SYSTEMS").Range("AN11").Value
Label6.Caption = Worksheets("SYSTEMS").Range("AN12").Value
Label7.Caption = Worksheets("SYSTEMS").Range("AN13").Value
Label8.Caption = Worksheets("SYSTEMS").Range("AN14").Value
Label9.Caption = Worksheets("SYSTEMS").Range("AN15").Value
Label10.Caption = Worksheets("SYSTEMS").Range("AN16").Value
Label11.Caption = Worksheets("SYSTEMS").Range("AN17").Value
Label12.Caption = Worksheets("SYSTEMS").Range("AN18").Value
Label13.Caption = Worksheets("SYSTEMS").Range("AN19").Value
Label14.Caption = Worksheets("SYSTEMS").Range("AN20").Value
Label15.Caption = Worksheets("SYSTEMS").Range("AN21").Value
Label16.Caption = Worksheets("SYSTEMS").Range("AN22").Value
Label17.Caption = Worksheets("SYSTEMS").Range("AN23").Value
Label18.Caption = Worksheets("SYSTEMS").Range("AN24").Value
Label19.Caption = Worksheets("SYSTEMS").Range("AO10").Value
Label20.Caption = Worksheets("SYSTEMS").Range("AO11").Value
Label21.Caption = Worksheets("SYSTEMS").Range("AO12").Value
Label22.Caption = Worksheets("SYSTEMS").Range("AO13").Value
Label23.Caption = Worksheets("SYSTEMS").Range("AO14").Value
Label24.Caption = Worksheets("SYSTEMS").Range("AO15").Value
Label25.Caption = Worksheets("SYSTEMS").Range("AO16").Value
Label26.Caption = Worksheets("SYSTEMS").Range("AO17").Value
Label27.Caption = Worksheets("SYSTEMS").Range("AO18").Value
Label28.Caption = Worksheets("SYSTEMS").Range("AO19").Value
Label29.Caption = Worksheets("SYSTEMS").Range("AO20").Value
Label30.Caption = Worksheets("SYSTEMS").Range("AO21").Value
Label31.Caption = Worksheets("SYSTEMS").Range("AO22").Value
Label32.Caption = Worksheets("SYSTEMS").Range("AO23").Value
Label33.Caption = Worksheets("SYSTEMS").Range("A024").Value
Label34.Caption = Worksheets("SYSTEMS").Range("AP10").Value
Label35.Caption = Worksheets("SYSTEMS").Range("AP11").Value
Label36.Caption = Worksheets("SYSTEMS").Range("AP12").Value
Label37.Caption = Worksheets("SYSTEMS").Range("AP13").Value
Label38.Caption = Worksheets("SYSTEMS").Range("AP14").Value
Label39.Caption = Worksheets("SYSTEMS").Range("AP15").Value
Label40.Caption = Worksheets("SYSTEMS").Range("AP16").Value
Label41.Caption = Worksheets("SYSTEMS").Range("AP17").Value
Label42.Caption = Worksheets("SYSTEMS").Range("AP18").Value
Label43.Caption = Worksheets("SYSTEMS").Range("AP19").Value
Label44.Caption = Worksheets("SYSTEMS").Range("AP20").Value
Label45.Caption = Worksheets("SYSTEMS").Range("AP21").Value
Label46.Caption = Worksheets("SYSTEMS").Range("AP22").Value
Label47.Caption = Worksheets("SYSTEMS").Range("AP23").Value
Label48.Caption = Worksheets("SYSTEMS").Range("AP24").Value
End Sub

 

[Norie]

I'm pretty sure that code can be tidied up - take a look at the Controls collection and loops.

But before I post any actual code I need to ask you why you have over 40 labels on a userform to display data.

Couldn't you use a listbox?

 

[glerwell]

Labels were suggested to me on this forum as was a novice with VBA.

The labels are actually the range (AN10:AP24) from a sheet called "SYSTEMS". I wanted a popup box to look as close as it could to the range on sheet and thats why I think labels were suggested.

thanks

 

[Norie]

Well I don't know why labels were suggested.

You could easily populate a listbox with data from that range like this.

Private Sub UserForm_Initialize()
    With ListBox1
        .ColumnCount = 3
        .RowSource = "AN10:AP24"
    End With
End Sub

As to your question about updating the data I think we need more information.

 

[glerwell]

That looks alot better!!!

Can I format the text colour for each column?

Regarding the more info, whenever the values of AN10:AP24 change I have to close the userform and rerun the macro which activated the userform to appear. Can the values in listbox change automatically when the values of AN10:AP24 are changed, without the need to close and re-run the userform.

 

[Norie]

First question - No.

Second question - how would these values be getting changed exactly.

That's what I mean about needing more information.

 

[glerwell]

These values are changed manually.

 

[Norie]

But when? How?

When do you need to display the data?

Usually you can't alter data on a worksheet while displaying a userform.

And it might be an idea to actually use the userform to do the data altering.

 

[bobinnc]

If you change the User Form property "Show Modal" to False, you can change the spreadsheet while the form is displayed. Since the listbox is tied to the range, the values will change on the form as the spreadsheet is changed. Also, you could use three listboxes with one column each and set the background color of them differently. You are limited to the system colors. This is cumbersome but should work.

 

[glerwell]

Hi bob

Did what you suggested and everythings fine. Is there way that I can add a command button to to copy the selection that is highlighted in the listbox to the clipboard, so I can paste the value in another sheet.

thanks