View Listbox items in the Label

[Shazi]

Hi,

I have a UserForm1 with Listbox1. the RowSource is [Sheet3!
B7:H1000]
Column Head are: Name, Company, Mobile, Phone1, Phone2, Fax & Email.

My problem is this, I want to see the Selected Row contents in
Lables.

I put 7 Lables in the Userform, names are:

Label1_Name, Label2_Company, Label3_Mobile like this...............

Pls help me out to solve this problem.

I hope you understand my question.

Regards.

Shahzad

 

[Incidental]

Hi Shahzad

One way to do this would be to have 7 labels with the standard names
i.e. Label1, Label2 then to use a loop to pass the value to the
caption of the Label controls. I have included the code below.

Option Explicit
Dim Ctrl As Control
Dim iNdx As Integer
Dim i As Integer

Private Sub ListBox1_Click()

iNdx = ListBox1.ListIndex

For i = 1 To 7

Set Ctrl = UserForm1.Controls("Label" & i)

Ctrl.Caption = ListBox1.List(iNdx, i - 1)

Set Ctrl = Nothing

Next

End Sub

I hope this helps

Steve

 

[Shazi]

Hi Shahzad

One way to do this would be to have 7 labels with the standard names
i.e. Label1, Label2 then to use a loop to pass the value to the
caption of the Label controls.  I have included the code below.

Option Explicit
Dim Ctrl As Control
Dim iNdx As Integer
Dim i As Integer

Private Sub ListBox1_Click()

iNdx = ListBox1.ListIndex

    For i = 1 To 7

        Set Ctrl = UserForm1.Controls("Label" & i)

            Ctrl.Caption = ListBox1.List(iNdx, i - 1)

        Set Ctrl = Nothing

    Next

End Sub

I hope this helps

Steve

Hi, Steve,

Sorry to say that in my userform your code is not working, I dont know
why.

My previous code is:

My List box contains: Col A= Name, Col B=Company, Col C=City like
this....


Private Sub ListBox1_Change()
Dim SourceData As Range
Dim Val1 As String, Val2 As String, Val3 As String

' Set SourceRange = Range(ListBox1.RowSource)

Val1 = ListBox1.Value
' Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1,
1).Value
' Val3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1,
1).Value

Label1.Caption = Val1
Label2.Caption = Val2
Label3.Caption = Val3

End Sub

The above code is working, and I am able to see Name in Label1. and
unable to see CompanyName and City in Label 2 and 3.

and If I open this line:

Set SourceRange = Range(ListBox1.RowSource)

then the error message is appearing saying, Compile Error, Variable
not define.

What to do in this case.

Thanks and regards.

Shahzad

 

[Incidental]

Hi Shahzad

Sorry for the late reply, i have had a look at your code and it works
fine if you declare SourceRange as a range you have SourceData
declared but it is not used within your code??

As for my own code i am unsure as to why it is not working for you but
it might help you understand what the code is doing if you create a
test userform and only use the basics (a listbox and 7 labels) all
with the default names, though the userform should be called UserForm1
and then step through the code 1 line at a time using the F8 key. If
you set your screen up to see both the userform and the VBE you can
watch what is happening though but you may not need or want to do that
it is entirely up to you.

As i said if you declare SourceRange as a range you should get your
code working, the error "Compile Error, Variable not defined" is
generated when using Option Explicit at the top of your code, this
checks your code at compile time to ensure all your variables are
correct among other things.

I hope this helps but if you need anything else or you would like me
to comment the code i posted originally then let me know.

Steve

 

[Shazi]

Hi Shahzad

Sorry for the late reply, i have had a look at your code and it works
fine if you declare SourceRange as a range you have SourceData
declared but it is not used within your code??

As for my own code i am unsure as to why it is not working for you but
it might help you understand what the code is doing if you create a
test userform and only use the basics (a listbox and 7 labels) all
with the default names, though the userform should be called UserForm1
and then step through the code 1 line at a time using the F8 key.  If
you set your screen up to see both the userform and the VBE you can
watch what is happening though but you may not need or want to do that
it is entirely up to you.

As i said if you declare SourceRange as a range you should get your
code working, the error "Compile Error, Variable not defined" is
generated when using Option Explicit at the top of your code, this
checks your code at compile time to ensure all your variables are
correct among other things.

I hope this helps but if you need anything else or you would like me
to comment the code i posted originally then let me know.

Steve

July 17, 2008

Hi, Mr. Steve,

Good Evening,

Thank you very much for your reply. I tested your code once again in
a test form as per your instructions. but sorry to say, its not
working, I dont know why. I am using Office 2003.

Any way, I made an other solution for my problem. which is as under:

Actually I want to make a Userform with one Listbox1 and one Exit
Button.
The ListBox1 shows the data from worksheet, the information came in
the listbox is very small font, I can increase the Listbox Font
Property, but instead of doing this I wanted to put 7 Labels on the
Userform below the Listbox, to show the Listbox items. like Name,
Company, City, Mobile, Rate, Unit Price etc.
for the Easy Viewing the Data in the UserForm with Big Fonts, out of
the ListBox. when I move Aero keys up and down all the information
will displayed in the Lables also.

for that purpose I make a procedure which is given below. Now its
working properly as per my wish, its making my life easy, and for
others.


Its very easy now: One UserForm1, One ListBox1, and 8 Labels are
required for this procedure.


Private Sub ListBox1_Change()
Dim SourceData As Range
Dim Val1 As String, Val2 As String, Val3 As String, Val4 As
String, Val5 As String, Val6 As String

Set SourceRange = Range(ListBox1.RowSource)

Val1 = ListBox1.Value
Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1,
1).Value
Val3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1,
1).Value
Val4 = SourceRange.Offset(ListBox1.ListIndex, 3).Resize(1,
1).Value
Val5 = SourceRange.Offset(ListBox1.ListIndex, 4).Resize(1,
1).Value
Val6 = SourceRange.Offset(ListBox1.ListIndex, 5).Resize(1,
1).Value
Val7 = SourceRange.Offset(ListBox1.ListIndex, 6).Resize(1,
1).Value
Val8 = SourceRange.Offset(ListBox1.ListIndex, 7).Resize(1,
1).Value
Val9 = SourceRange.Offset(ListBox1.ListIndex, 8).Resize(1,
1).Value


Label3.Caption = Val1
Label4.Caption = Val2
Label5.Caption = Val3
Label9.Caption = Val4
Label10.Caption = Val5
Label16.Caption = Val6
Label17.Caption = Val7
Label18.Caption = Val8

End Sub

Mr. Steve, the above function is now working Excccccellllllllllllent.

Thanks for your reply and continious support to me.

with best regards.


Syed Shahzad Zafar
Madinah