Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row source question.
I have a user form that contains a combo box.
The rowsource is a named range in Excel. If I put the named range in the properties box of the combobox on the form, the form and combobox control appears when called but the combo box is blank. The row souce is on a different worksheet inthe same workbook from the sheet that is active when the form is called. I have tried renaming the rowsource when the form is initialized as follows (Iterations commented out). Private Sub UserForm_Initialize() Dim ws As Worksheet Set ws = Worksheets("Imp") 'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data against current weeksheet, where data exists. 'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major eventlist is a rangename) 'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank data agianst a separte sheet where data exists) End Sub Whether I use: Me.ctrlEventList.RowSource = MajorEventList or Me.ctrlEventList.RowSource = "'Major Events'!b1:b30" I get no error message, but I also get no data showing in the form. I call the form using: frmSelectMajorEvent.Show When it opens, it should initialize itself, right? Or do I need to open the form and also call the initalize routine to initialize the data source? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row source question.
Your first line of code worked fine for me:
Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" I like this syntax better: Me.ctrlMajorEventList.RowSource _ = worksheets("Imp").range("d1:d32").address(external :=true) But that's just my preference--your code worked fine for me. When you say that the combobox was blank, do you mean the value of the combobox was blank--or that the dropdown showed nothing (an empty list)? If you wanted to show a choice in that combobox, you could add: With Me.ctrlmajorEventList .RowSource = Worksheets("imp").Range("D1:d32").Address(external :=True) .ListIndex = 0 'or 0 to 31 (0 based) End With But I bet you didn't mean that. When you use the dropdown arrow, do you see a very short dropdown area--like there's nothing assigned to the combobox? Or do you see a dropdown that looks like it would support 32 entries? If you see the first, then something bad is happening (I don't have a clue). But if you see the second, I'd check to make sure that D1:D32 on Imp actually has data in it. ps. And one more thing to make sure it's not a pc display problem. If you close excel, then reopen it, then open your workbook and show the userform, does it appear ok? Hydra wrote: I have a user form that contains a combo box. The rowsource is a named range in Excel. If I put the named range in the properties box of the combobox on the form, the form and combobox control appears when called but the combo box is blank. The row souce is on a different worksheet inthe same workbook from the sheet that is active when the form is called. I have tried renaming the rowsource when the form is initialized as follows (Iterations commented out). Private Sub UserForm_Initialize() Dim ws As Worksheet Set ws = Worksheets("Imp") 'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data against current weeksheet, where data exists. 'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major eventlist is a rangename) 'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank data agianst a separte sheet where data exists) End Sub Whether I use: Me.ctrlEventList.RowSource = MajorEventList or Me.ctrlEventList.RowSource = "'Major Events'!b1:b30" I get no error message, but I also get no data showing in the form. I call the form using: frmSelectMajorEvent.Show When it opens, it should initialize itself, right? Or do I need to open the form and also call the initalize routine to initialize the data source? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row source question.
None of them have worked for me.
I get a form with a command button to close the form I get a control for the combo box, with a cursor in the box, but no data. the combo box has a scroll bar, but it goes full screen, there being nothing to scroll to. What Does ----- address(external:=true) ----Do? What i really want is Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major event is a different sheet. Does that mean I ahave to define ws as "Major Event" instead of "Imp" which is the active sheet?? Closing and reopening excel has no effect. I have trid different ranges with different kinds of data. I cannot assign the row source directly in the properties table: that throws an error. "Dave Peterson" wrote: Your first line of code worked fine for me: Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" I like this syntax better: Me.ctrlMajorEventList.RowSource _ = worksheets("Imp").range("d1:d32").address(external :=true) But that's just my preference--your code worked fine for me. When you say that the combobox was blank, do you mean the value of the combobox was blank--or that the dropdown showed nothing (an empty list)? If you wanted to show a choice in that combobox, you could add: With Me.ctrlmajorEventList .RowSource = Worksheets("imp").Range("D1:d32").Address(external :=True) .ListIndex = 0 'or 0 to 31 (0 based) End With But I bet you didn't mean that. When you use the dropdown arrow, do you see a very short dropdown area--like there's nothing assigned to the combobox? Or do you see a dropdown that looks like it would support 32 entries? If you see the first, then something bad is happening (I don't have a clue). But if you see the second, I'd check to make sure that D1:D32 on Imp actually has data in it. ps. And one more thing to make sure it's not a pc display problem. If you close excel, then reopen it, then open your workbook and show the userform, does it appear ok? Hydra wrote: I have a user form that contains a combo box. The rowsource is a named range in Excel. If I put the named range in the properties box of the combobox on the form, the form and combobox control appears when called but the combo box is blank. The row souce is on a different worksheet inthe same workbook from the sheet that is active when the form is called. I have tried renaming the rowsource when the form is initialized as follows (Iterations commented out). Private Sub UserForm_Initialize() Dim ws As Worksheet Set ws = Worksheets("Imp") 'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data against current weeksheet, where data exists. 'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major eventlist is a rangename) 'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank data agianst a separte sheet where data exists) End Sub Whether I use: Me.ctrlEventList.RowSource = MajorEventList or Me.ctrlEventList.RowSource = "'Major Events'!b1:b30" I get no error message, but I also get no data showing in the form. I call the form using: frmSelectMajorEvent.Show When it opens, it should initialize itself, right? Or do I need to open the form and also call the initalize routine to initialize the data source? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row source question.
Try this:
msgbox worksheets("Imp").range("d1:d32").address(external :=true) You'll see that it includes the workbook name, worksheet name and the cell address. I'm not sure what full screen is, but if that means it looks like you could fit 32 entries in that area, then I'd look back at the worksheet and check to see what's in that range. Maybe you're off by a column. Hydra wrote: None of them have worked for me. I get a form with a command button to close the form I get a control for the combo box, with a cursor in the box, but no data. the combo box has a scroll bar, but it goes full screen, there being nothing to scroll to. What Does ----- address(external:=true) ----Do? What i really want is Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major event is a different sheet. Does that mean I ahave to define ws as "Major Event" instead of "Imp" which is the active sheet?? Closing and reopening excel has no effect. I have trid different ranges with different kinds of data. I cannot assign the row source directly in the properties table: that throws an error. "Dave Peterson" wrote: Your first line of code worked fine for me: Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" I like this syntax better: Me.ctrlMajorEventList.RowSource _ = worksheets("Imp").range("d1:d32").address(external :=true) But that's just my preference--your code worked fine for me. When you say that the combobox was blank, do you mean the value of the combobox was blank--or that the dropdown showed nothing (an empty list)? If you wanted to show a choice in that combobox, you could add: With Me.ctrlmajorEventList .RowSource = Worksheets("imp").Range("D1:d32").Address(external :=True) .ListIndex = 0 'or 0 to 31 (0 based) End With But I bet you didn't mean that. When you use the dropdown arrow, do you see a very short dropdown area--like there's nothing assigned to the combobox? Or do you see a dropdown that looks like it would support 32 entries? If you see the first, then something bad is happening (I don't have a clue). But if you see the second, I'd check to make sure that D1:D32 on Imp actually has data in it. ps. And one more thing to make sure it's not a pc display problem. If you close excel, then reopen it, then open your workbook and show the userform, does it appear ok? Hydra wrote: I have a user form that contains a combo box. The rowsource is a named range in Excel. If I put the named range in the properties box of the combobox on the form, the form and combobox control appears when called but the combo box is blank. The row souce is on a different worksheet inthe same workbook from the sheet that is active when the form is called. I have tried renaming the rowsource when the form is initialized as follows (Iterations commented out). Private Sub UserForm_Initialize() Dim ws As Worksheet Set ws = Worksheets("Imp") 'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data against current weeksheet, where data exists. 'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major eventlist is a rangename) 'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank data agianst a separte sheet where data exists) End Sub Whether I use: Me.ctrlEventList.RowSource = MajorEventList or Me.ctrlEventList.RowSource = "'Major Events'!b1:b30" I get no error message, but I also get no data showing in the form. I call the form using: frmSelectMajorEvent.Show When it opens, it should initialize itself, right? Or do I need to open the form and also call the initalize routine to initialize the data source? -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row source question.
Well, if I add a listbox to the form, with the same row source, then it works.
I deleted the combo box and put in a new one with a different name, sme row source, same blank result. "Dave Peterson" wrote: Try this: msgbox worksheets("Imp").range("d1:d32").address(external :=true) You'll see that it includes the workbook name, worksheet name and the cell address. I'm not sure what full screen is, but if that means it looks like you could fit 32 entries in that area, then I'd look back at the worksheet and check to see what's in that range. Maybe you're off by a column. Hydra wrote: None of them have worked for me. I get a form with a command button to close the form I get a control for the combo box, with a cursor in the box, but no data. the combo box has a scroll bar, but it goes full screen, there being nothing to scroll to. What Does ----- address(external:=true) ----Do? What i really want is Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major event is a different sheet. Does that mean I ahave to define ws as "Major Event" instead of "Imp" which is the active sheet?? Closing and reopening excel has no effect. I have trid different ranges with different kinds of data. I cannot assign the row source directly in the properties table: that throws an error. "Dave Peterson" wrote: Your first line of code worked fine for me: Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" I like this syntax better: Me.ctrlMajorEventList.RowSource _ = worksheets("Imp").range("d1:d32").address(external :=true) But that's just my preference--your code worked fine for me. When you say that the combobox was blank, do you mean the value of the combobox was blank--or that the dropdown showed nothing (an empty list)? If you wanted to show a choice in that combobox, you could add: With Me.ctrlmajorEventList .RowSource = Worksheets("imp").Range("D1:d32").Address(external :=True) .ListIndex = 0 'or 0 to 31 (0 based) End With But I bet you didn't mean that. When you use the dropdown arrow, do you see a very short dropdown area--like there's nothing assigned to the combobox? Or do you see a dropdown that looks like it would support 32 entries? If you see the first, then something bad is happening (I don't have a clue). But if you see the second, I'd check to make sure that D1:D32 on Imp actually has data in it. ps. And one more thing to make sure it's not a pc display problem. If you close excel, then reopen it, then open your workbook and show the userform, does it appear ok? Hydra wrote: I have a user form that contains a combo box. The rowsource is a named range in Excel. If I put the named range in the properties box of the combobox on the form, the form and combobox control appears when called but the combo box is blank. The row souce is on a different worksheet inthe same workbook from the sheet that is active when the form is called. I have tried renaming the rowsource when the form is initialized as follows (Iterations commented out). Private Sub UserForm_Initialize() Dim ws As Worksheet Set ws = Worksheets("Imp") 'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data against current weeksheet, where data exists. 'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major eventlist is a rangename) 'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank data agianst a separte sheet where data exists) End Sub Whether I use: Me.ctrlEventList.RowSource = MajorEventList or Me.ctrlEventList.RowSource = "'Major Events'!b1:b30" I get no error message, but I also get no data showing in the form. I call the form using: frmSelectMajorEvent.Show When it opens, it should initialize itself, right? Or do I need to open the form and also call the initalize routine to initialize the data source? -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row source question.
A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32"
works OK But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32" It thows an error. The combo box won't work with either one. Deleted the combobox and put in a new one, same result. "Dave Peterson" wrote: Try this: msgbox worksheets("Imp").range("d1:d32").address(external :=true) You'll see that it includes the workbook name, worksheet name and the cell address. I'm not sure what full screen is, but if that means it looks like you could fit 32 entries in that area, then I'd look back at the worksheet and check to see what's in that range. Maybe you're off by a column. Hydra wrote: None of them have worked for me. I get a form with a command button to close the form I get a control for the combo box, with a cursor in the box, but no data. the combo box has a scroll bar, but it goes full screen, there being nothing to scroll to. What Does ----- address(external:=true) ----Do? What i really want is Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major event is a different sheet. Does that mean I ahave to define ws as "Major Event" instead of "Imp" which is the active sheet?? Closing and reopening excel has no effect. I have trid different ranges with different kinds of data. I cannot assign the row source directly in the properties table: that throws an error. "Dave Peterson" wrote: Your first line of code worked fine for me: Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" I like this syntax better: Me.ctrlMajorEventList.RowSource _ = worksheets("Imp").range("d1:d32").address(external :=true) But that's just my preference--your code worked fine for me. When you say that the combobox was blank, do you mean the value of the combobox was blank--or that the dropdown showed nothing (an empty list)? If you wanted to show a choice in that combobox, you could add: With Me.ctrlmajorEventList .RowSource = Worksheets("imp").Range("D1:d32").Address(external :=True) .ListIndex = 0 'or 0 to 31 (0 based) End With But I bet you didn't mean that. When you use the dropdown arrow, do you see a very short dropdown area--like there's nothing assigned to the combobox? Or do you see a dropdown that looks like it would support 32 entries? If you see the first, then something bad is happening (I don't have a clue). But if you see the second, I'd check to make sure that D1:D32 on Imp actually has data in it. ps. And one more thing to make sure it's not a pc display problem. If you close excel, then reopen it, then open your workbook and show the userform, does it appear ok? Hydra wrote: I have a user form that contains a combo box. The rowsource is a named range in Excel. If I put the named range in the properties box of the combobox on the form, the form and combobox control appears when called but the combo box is blank. The row souce is on a different worksheet inthe same workbook from the sheet that is active when the form is called. I have tried renaming the rowsource when the form is initialized as follows (Iterations commented out). Private Sub UserForm_Initialize() Dim ws As Worksheet Set ws = Worksheets("Imp") 'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data against current weeksheet, where data exists. 'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major eventlist is a rangename) 'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank data agianst a separte sheet where data exists) End Sub Whether I use: Me.ctrlEventList.RowSource = MajorEventList or Me.ctrlEventList.RowSource = "'Major Events'!b1:b30" I get no error message, but I also get no data showing in the form. I call the form using: frmSelectMajorEvent.Show When it opens, it should initialize itself, right? Or do I need to open the form and also call the initalize routine to initialize the data source? -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row source question.
Try using the other syntax in a brand new test workbook.
Hydra wrote: A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32" works OK But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32" It thows an error. The combo box won't work with either one. Deleted the combobox and put in a new one, same result. "Dave Peterson" wrote: Try this: msgbox worksheets("Imp").range("d1:d32").address(external :=true) You'll see that it includes the workbook name, worksheet name and the cell address. I'm not sure what full screen is, but if that means it looks like you could fit 32 entries in that area, then I'd look back at the worksheet and check to see what's in that range. Maybe you're off by a column. Hydra wrote: None of them have worked for me. I get a form with a command button to close the form I get a control for the combo box, with a cursor in the box, but no data. the combo box has a scroll bar, but it goes full screen, there being nothing to scroll to. What Does ----- address(external:=true) ----Do? What i really want is Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major event is a different sheet. Does that mean I ahave to define ws as "Major Event" instead of "Imp" which is the active sheet?? Closing and reopening excel has no effect. I have trid different ranges with different kinds of data. I cannot assign the row source directly in the properties table: that throws an error. "Dave Peterson" wrote: Your first line of code worked fine for me: Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" I like this syntax better: Me.ctrlMajorEventList.RowSource _ = worksheets("Imp").range("d1:d32").address(external :=true) But that's just my preference--your code worked fine for me. When you say that the combobox was blank, do you mean the value of the combobox was blank--or that the dropdown showed nothing (an empty list)? If you wanted to show a choice in that combobox, you could add: With Me.ctrlmajorEventList .RowSource = Worksheets("imp").Range("D1:d32").Address(external :=True) .ListIndex = 0 'or 0 to 31 (0 based) End With But I bet you didn't mean that. When you use the dropdown arrow, do you see a very short dropdown area--like there's nothing assigned to the combobox? Or do you see a dropdown that looks like it would support 32 entries? If you see the first, then something bad is happening (I don't have a clue). But if you see the second, I'd check to make sure that D1:D32 on Imp actually has data in it. ps. And one more thing to make sure it's not a pc display problem. If you close excel, then reopen it, then open your workbook and show the userform, does it appear ok? Hydra wrote: I have a user form that contains a combo box. The rowsource is a named range in Excel. If I put the named range in the properties box of the combobox on the form, the form and combobox control appears when called but the combo box is blank. The row souce is on a different worksheet inthe same workbook from the sheet that is active when the form is called. I have tried renaming the rowsource when the form is initialized as follows (Iterations commented out). Private Sub UserForm_Initialize() Dim ws As Worksheet Set ws = Worksheets("Imp") 'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data against current weeksheet, where data exists. 'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major eventlist is a rangename) 'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank data agianst a separte sheet where data exists) End Sub Whether I use: Me.ctrlEventList.RowSource = MajorEventList or Me.ctrlEventList.RowSource = "'Major Events'!b1:b30" I get no error message, but I also get no data showing in the form. I call the form using: frmSelectMajorEvent.Show When it opens, it should initialize itself, right? Or do I need to open the form and also call the initalize routine to initialize the data source? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Basic Pivot Table question re adding rows to source data and refre | Excel Discussion (Misc queries) | |||
How change link source in long formula when source moved | Excel Programming | |||
How can i set the source-data-range of pivottable2 to the source . | Excel Discussion (Misc queries) | |||
How can I change the source of my extern source database in an ex. | Excel Programming | |||
Pivot table source data question | Excel Programming |