Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForm ComboBox RowSource Question | Excel Programming | |||
TextBox RowSource Question | Excel Programming | |||
help with rowsource | Excel Programming | |||
combobox rowsource | Excel Programming | |||
Using IF, Then with rowsource? | Excel Programming |