Load & Access a UserForm ComboBox

[Minitman]

Greetings,

I have a range of cells that is 27 rows tall and 105 columns wide.
Column C has the information that needs to appear in the ComboBox on
the UserForm. At this time, it is the information in Column D that is
showing up.

Here is the code that loads the ComboBox called I_3:
__________________________________________________________________

Public Sub Load_I3_List()

ws1_2.Activate 'Sheet with the named range on it
Set rListInput = _
ws1_2.Range("rInput" & iTD) 'The named range
With I_3 'the ComboBox
.RowSource = "'Input'!" & rListInput.Address
.ColumnCount = rListInput.Columns.Count
.ListIndex = 0
End With
I_3.Value = I_3.ListIndex + 1

End Sub
__________________________________________________________________

I am not sure how much of rInput1 (with iTD = 1) is actually loading.
I need to have the code enter the rest of the row into the rest of the
104 TextBoxes called I_1 thru I_105 (minus I_3). They correspond to
the columns on the sheet where column A = 0, B=1, C=3 etc. Column C
is the column with the reference name used elsewhere.

If this question is to vague, I will gladly answer any questions that
might arise.

Does anyone have any ideas as to how to do this?

Any help would be greatly appreciated.

-Minitman

 

[JLGWhiz]

You can put a message box in the code (see below) or do a Debug.Print to
see what the actual rowsource address is. Your named range might not be
what you think it is. I noticed that when you set the object variable you
use ws1_2 as the Sheet reference, but when you assign the RowSource you use
"Input" as the Sheet reference. That could be your problem unless you have a
global variable set up for ws1_2 to equal "Input"

Public Sub Load_I3_List()

ws1_2.Activate 'Sheet with the named range on it
Set rListInput = _
ws1_2.Range("rInput" & iTD) 'The named range
MsgBox rListInput.Address
With I_3 'the ComboBox
.RowSource = "'Input'!" & rListInput.Address
.ColumnCount = rListInput.Columns.Count
.ListIndex = 0
End With
I_3.Value = I_3.ListIndex + 1

End Sub

 

[Minitman]

Hey JLGWhiz,

Thanks for the reply.

It is interesting that you should global variables. I can't seem to
make them work!!!

I have 2 or more workbooks open at a time and would like to set their
names as variables to be used by all three.

The convention I came up with is wb1 is always variable (it will be
the named the current year-month.xls (2008-08.xls for this month or
2008-09.xls for next month). This is a monthly scheduler.

The second workbook is called wb2. It will always be MCL6.xls (This
is a list of customers). This list is used by all of the workbooks.

When I load scheduler, I automatically load MCL6.xls though the
Workbook_Open event. I am still working on the rest of the workbooks
as to timing and linking - not the subject of this question.

For the scheduler since it is variable, I would like to use wb1 with
the sheets set to:

wb1 = ThisWorkbook
ws1_1 = wb1.worksheets("Sheet1")
ws1_2 = wb1.worksheets("Sheet2")

And MCL6.xls set to:

wb2 = Workbooks("MCL6.xls")
ws2_1 = wb2.worksheets("Sheet1")
ws2_2 = wb2.worksheets("Sheet2")

The rest of the workbooks are merely forms 1 sheet each.

Also these same variable are needed in some of the sheets code
sections. MCL6 has a form made on ws2_2 (sheet2 of MCL6) that is
sometimes filled out to add new customers or change existing customer
information.

How and where do I place these variables to make them global
variables?

Any help would be appreciated.

-Minitman

 

[JLGWhiz]

Global variables are placed at the top of the standard Module1, or Module2,
etc. Not in the Worksheet code module and not in any form or control code
module.

Syntax is: Public myVar = "SomeValue" 'you substitue variable and value.

See Chip Pearson's web site for full explanation.

http://www.cpearson.com/excel/TrulyGlobalVariables.htm

 

[Minitman]

Hey JLGWhiz,

Thanks for the reply.

I don't understand where I am supposed to place this. Is this code or
a declaration?

Public myVar = "SomeValue" after I substitute my names,
Public wb1 = ThisWorkbook

Debug doesn't like it.

I looked at the info at that site you left the link for (Thanks for
that, great stuff), but unless I didn't understand what was being
said, it appears that the global variables they were talking about are
limited to only long numbers. I would like to replace the workbook
name with a variable, which is not a number.

All I want to do is assign a variable name to one workbook to be used
by 3 other workbooks (all open). The only reason for the one is that
I never know what the name of that workbook is going to be (it's a
date - eg. 2008-08.xls for the figures in August of 2008)

-Minitman