Rowsource Question

[Noah]

I originally posted this question on 11/14, but I still haven't been able to
figure out how to solve the problem based on the response that I got. So I
am posting the question again in more detail.

If Noah is the user, I want the RowSource of ListBox1 in UserForm1 to fill
with values from Sheet1. If Joe is the user, I want the RowSource of
ListBox1 in UserForm1 to fill with values from Sheet2. I am not sure if this
is the right think to do, but I have left the RowSource field in the
Properties of ListBox1 empty.
-----------------------
I currently have the following code in Module1:
Public rng as Range

Sub Macro1()
Dim User As String
User = Environ("UserName")
Select Case User
Case "Noah"
lastrow = Sheet1.Cells(1, 1).End(xlDown).Row
rng = Sheet1.Range(Cells(1, 1), Cells(lastrow, 2))
UserForm1.Show
Case �Joe�
lastrow = Sheet2.Cells(1, 1).End(xlDown).Row
rng = Sheet2.Range(Cells(1, 1), Cells(lastrow, 2))
UserForm1.Show
Case Else
End Select
End Sub
-----------------------
I currently have the following code in the code module for UserForm1:

Private Sub UserForm_Initialize()
Me.ListBox1.RowSource = rng.Address
End Sub
------------------------

The error message that I keep getting is: "Could not set the RowSource
property. Invalid property value." Please help! Thanks!

[chijanzen]

Hi Noah:

try

'Module
Public rng As Range
Sub Macro1()
Dim User As String
User = Environ("UserName")
Select Case User
Case "Noah"
lastrow = Sheet1.Cells(1, 1).End(xlDown).Row
Set rng = Sheet1.Range(Cells(1, 1), Cells(lastrow, 2))
UserForm1.Show
Case "Joe"
lastrow = Sheet2.Cells(1, 1).End(xlDown).Row
Set rng = Sheet2.Range(Cells(1, 1), Cells(lastrow, 2))
UserForm1.Show
Case Else
End Select
End Sub

'Userform
Private Sub UserForm_Initialize()
Me.ListBox1.ColumnCount = 2
Me.ListBox1.ColumnWidths = "20;20"
Me.ListBox1.RowSource = rng.Address
End Sub


--
天行健，君�*�以自強不息
地勢坤，君�*�以厚德載物

http://www.vba.com.tw/plog/


"Noah" wrote:

> I originally posted this question on 11/14, but I still haven't been able to
> figure out how to solve the problem based on the response that I got. So I
> am posting the question again in more detail.
>
> If Noah is the user, I want the RowSource of ListBox1 in UserForm1 to fill
> with values from Sheet1. If Joe is the user, I want the RowSource of
> ListBox1 in UserForm1 to fill with values from Sheet2. I am not sure if this
> is the right think to do, but I have left the RowSource field in the
> Properties of ListBox1 empty.
> -----------------------
> I currently have the following code in Module1:
> Public rng as Range
>
> Sub Macro1()
> Dim User As String
> User = Environ("UserName")
> Select Case User
> Case "Noah"
> lastrow = Sheet1.Cells(1, 1).End(xlDown).Row
> rng = Sheet1.Range(Cells(1, 1), Cells(lastrow, 2))
> UserForm1.Show
> Case “Joe”
> lastrow = Sheet2.Cells(1, 1).End(xlDown).Row
> rng = Sheet2.Range(Cells(1, 1), Cells(lastrow, 2))
> UserForm1.Show
> Case Else
> End Select
> End Sub
> -----------------------
> I currently have the following code in the code module for UserForm1:
>
> Private Sub UserForm_Initialize()
> Me.ListBox1.RowSource = rng.Address
> End Sub
> ------------------------
>
> The error message that I keep getting is: "Could not set the RowSource
> property. Invalid property value." Please help! Thanks!

[Bob Phillips]

Noah,

Try this in the code module

Sub Macro1()
Dim User As String
Dim lastrow As Long
User = Environ("UserName")
Select Case User
Case "Noah"
lastrow = Sheet1.Cells(1, 1).End(xlDown).Row
Set rng = Sheet1.Range(Cells(1, 1), Cells(lastrow, 2))
UserForm1.Show
Case "Bob"
lastrow = Sheet2.Cells(1, 1).End(xlDown).Row
Set rng = Sheet2.Range(Cells(1, 1), Cells(lastrow, 2))
UserForm1.Show
Case Else
End Select
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Noah" <[email protected]> wrote in message
news:[email protected]...
> I originally posted this question on 11/14, but I still haven't been able
to
> figure out how to solve the problem based on the response that I got. So
I
> am posting the question again in more detail.
>
> If Noah is the user, I want the RowSource of ListBox1 in UserForm1 to fill
> with values from Sheet1. If Joe is the user, I want the RowSource of
> ListBox1 in UserForm1 to fill with values from Sheet2. I am not sure if
this
> is the right think to do, but I have left the RowSource field in the
> Properties of ListBox1 empty.
> -----------------------
> I currently have the following code in Module1:
> Public rng as Range
>
> Sub Macro1()
> Dim User As String
> User = Environ("UserName")
> Select Case User
> Case "Noah"
> lastrow = Sheet1.Cells(1, 1).End(xlDown).Row
> rng = Sheet1.Range(Cells(1, 1), Cells(lastrow, 2))
> UserForm1.Show
> Case "Joe"
> lastrow = Sheet2.Cells(1, 1).End(xlDown).Row
> rng = Sheet2.Range(Cells(1, 1), Cells(lastrow, 2))
> UserForm1.Show
> Case Else
> End Select
> End Sub
> -----------------------
> I currently have the following code in the code module for UserForm1:
>
> Private Sub UserForm_Initialize()
> Me.ListBox1.RowSource = rng.Address
> End Sub
> ------------------------
>
> The error message that I keep getting is: "Could not set the RowSource
> property. Invalid property value." Please help! Thanks!

[Noah]

It works! Thanks alot!

"chijanzen" wrote:

> Hi Noah:
>
> try
>
> 'Module
> Public rng As Range
> Sub Macro1()
> Dim User As String
> User = Environ("UserName")
> Select Case User
> Case "Noah"
> lastrow = Sheet1.Cells(1, 1).End(xlDown).Row
> Set rng = Sheet1.Range(Cells(1, 1), Cells(lastrow, 2))
> UserForm1.Show
> Case "Joe"
> lastrow = Sheet2.Cells(1, 1).End(xlDown).Row
> Set rng = Sheet2.Range(Cells(1, 1), Cells(lastrow, 2))
> UserForm1.Show
> Case Else
> End Select
> End Sub
>
> 'Userform
> Private Sub UserForm_Initialize()
> Me.ListBox1.ColumnCount = 2
> Me.ListBox1.ColumnWidths = "20;20"
> Me.ListBox1.RowSource = rng.Address
> End Sub
>
>
> --
> 天行健，君�*�以自強不息
> 地勢坤，君�*�以厚德載物
>
> http://www.vba.com.tw/plog/
>
>
> "Noah" wrote:
>
> > I originally posted this question on 11/14, but I still haven't been able to
> > figure out how to solve the problem based on the response that I got. So I
> > am posting the question again in more detail.
> >
> > If Noah is the user, I want the RowSource of ListBox1 in UserForm1 to fill
> > with values from Sheet1. If Joe is the user, I want the RowSource of
> > ListBox1 in UserForm1 to fill with values from Sheet2. I am not sure if this
> > is the right think to do, but I have left the RowSource field in the
> > Properties of ListBox1 empty.
> > -----------------------
> > I currently have the following code in Module1:
> > Public rng as Range
> >
> > Sub Macro1()
> > Dim User As String
> > User = Environ("UserName")
> > Select Case User
> > Case "Noah"
> > lastrow = Sheet1.Cells(1, 1).End(xlDown).Row
> > rng = Sheet1.Range(Cells(1, 1), Cells(lastrow, 2))
> > UserForm1.Show
> > Case “Joe”
> > lastrow = Sheet2.Cells(1, 1).End(xlDown).Row
> > rng = Sheet2.Range(Cells(1, 1), Cells(lastrow, 2))
> > UserForm1.Show
> > Case Else
> > End Select
> > End Sub
> > -----------------------
> > I currently have the following code in the code module for UserForm1:
> >
> > Private Sub UserForm_Initialize()
> > Me.ListBox1.RowSource = rng.Address
> > End Sub
> > ------------------------
> >
> > The error message that I keep getting is: "Could not set the RowSource
> > property. Invalid property value." Please help! Thanks!

[Noah]

My problem with the listbox was taken care of...but I closed the file and
opened it again, andnow the macro doesn't work. The problem seems to be with
my select case arguments. I think that it stores the value of lastrow and
rng in the case "Noah" part even if "Joe" is the user. The error message
that I get is "Object variable or with block variable not set". Any ideas?


Sub Macro1()
Dim User As String
User = Environ("UserName")
Select Case User
Case "Noah"
lastrow = Sheet1.Cells(1, 1).End(xlDown).Row
rng = Sheet1.Range(Cells(1, 1), Cells(lastrow, 2))
UserForm1.Show
Case “Joe”
lastrow = Sheet2.Cells(1, 1).End(xlDown).Row
rng = Sheet2.Range(Cells(1, 1), Cells(lastrow, 2))
UserForm1.Show
Case Else
End Select
End Sub



"Noah" wrote:

> It works! Thanks alot!
>
> "chijanzen" wrote:
>
> > Hi Noah:
> >
> > try
> >
> > 'Module
> > Public rng As Range
> > Sub Macro1()
> > Dim User As String
> > User = Environ("UserName")
> > Select Case User
> > Case "Noah"
> > lastrow = Sheet1.Cells(1, 1).End(xlDown).Row
> > Set rng = Sheet1.Range(Cells(1, 1), Cells(lastrow, 2))
> > UserForm1.Show
> > Case "Joe"
> > lastrow = Sheet2.Cells(1, 1).End(xlDown).Row
> > Set rng = Sheet2.Range(Cells(1, 1), Cells(lastrow, 2))
> > UserForm1.Show
> > Case Else
> > End Select
> > End Sub
> >
> > 'Userform
> > Private Sub UserForm_Initialize()
> > Me.ListBox1.ColumnCount = 2
> > Me.ListBox1.ColumnWidths = "20;20"
> > Me.ListBox1.RowSource = rng.Address
> > End Sub
> >
> >
> > --
> > 天行健，君�*�以自強不息
> > 地勢坤，君�*�以厚德載物
> >
> > http://www.vba.com.tw/plog/
> >
> >
> > "Noah" wrote:
> >
> > > I originally posted this question on 11/14, but I still haven't been able to
> > > figure out how to solve the problem based on the response that I got. So I
> > > am posting the question again in more detail.
> > >
> > > If Noah is the user, I want the RowSource of ListBox1 in UserForm1 to fill
> > > with values from Sheet1. If Joe is the user, I want the RowSource of
> > > ListBox1 in UserForm1 to fill with values from Sheet2. I am not sure if this
> > > is the right think to do, but I have left the RowSource field in the
> > > Properties of ListBox1 empty.
> > > -----------------------
> > > I currently have the following code in Module1:
> > > Public rng as Range
> > >
> > > Sub Macro1()
> > > Dim User As String
> > > User = Environ("UserName")
> > > Select Case User
> > > Case "Noah"
> > > lastrow = Sheet1.Cells(1, 1).End(xlDown).Row
> > > rng = Sheet1.Range(Cells(1, 1), Cells(lastrow, 2))
> > > UserForm1.Show
> > > Case “Joe”
> > > lastrow = Sheet2.Cells(1, 1).End(xlDown).Row
> > > rng = Sheet2.Range(Cells(1, 1), Cells(lastrow, 2))
> > > UserForm1.Show
> > > Case Else
> > > End Select
> > > End Sub
> > > -----------------------
> > > I currently have the following code in the code module for UserForm1:
> > >
> > > Private Sub UserForm_Initialize()
> > > Me.ListBox1.RowSource = rng.Address
> > > End Sub
> > > ------------------------
> > >
> > > The error message that I keep getting is: "Could not set the RowSource
> > > property. Invalid property value." Please help! Thanks!