ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Rowsource Question (https://www.excelbanter.com/excel-programming/345844-rowsource-question.html)

Noah
Rowsource Question
 
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
Rowsource Question
 
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[_6_]
Rowsource Question
 
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" wrote in message
...
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
Rowsource Question
 
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
Rowsource Question
 
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!


All times are GMT +1. The time now is 12:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com