Previously, we used a sheet (paper) to order our supplies, then key in what
was needed. In trying to go paperless, I have programmed excel for most of
what I need to do. However, I need help with the following:

I have a macro that will read data (list of items stocked and its max qty)
into an array when the user clicks the button "Begin Order". It gets the
data from a list on a worksheet in the same workbook. Then there will be a
userform that displays the list one item at a time.

The userform will display one item, and its max qty, at a time. The user
will enter the amount on hand and the macro will calculate amount needed to
order. Each time the user hits ENTER, the amount needed to order will be
entered in the form on the worksheet and the next item will display in the
userform.

How do I get the data, that is initially read into an array, to the userform
one at a time?

Thanks,
Les

You have a few methods

1) Make the array public so the userform and the main code will both get the
same array
2) Put the code that put the data into the array in the userform code
3) Make the code that reads the worksheet a seperate subroutine that returns
the array like this

Sub Main() 'this code can be in the userform

Dim MyArray() As Variant

Call ReadArray(MyArray())

End Sub


Sub ReadArray(ByRef MyArray() As Variant)

ReDim MyArray(100)
MyArray(3) = 22
'code to fill array
End Sub



"WLMPilot" wrote:

Previously, we used a sheet (paper) to order our supplies, then key in what
was needed. In trying to go paperless, I have programmed excel for most of
what I need to do. However, I need help with the following:

I have a macro that will read data (list of items stocked and its max qty)
into an array when the user clicks the button "Begin Order". It gets the
data from a list on a worksheet in the same workbook. Then there will be a
userform that displays the list one item at a time.

The userform will display one item, and its max qty, at a time. The user
will enter the amount on hand and the macro will calculate amount needed to
order. Each time the user hits ENTER, the amount needed to order will be
entered in the form on the worksheet and the next item will display in the
userform.

How do I get the data, that is initially read into an array, to the userform
one at a time?

Thanks,
Les

Thanks for your reply, but I have another questions concerning your response.

Trying to remember back for it has been a while. I am thinking that after
the ENTER button is clicked and the data is placed in the worksheet for that
stock item, then there is code to display (execute) the userform in order to
display it again with the next stock item.

If this is so, wouldn't options 2 and 3 read the data into the array
everytime instead of just once for the entire process?

Les

"Joel" wrote:

You have a few methods

1) Make the array public so the userform and the main code will both get the
same array
2) Put the code that put the data into the array in the userform code
3) Make the code that reads the worksheet a seperate subroutine that returns
the array like this

Sub Main() 'this code can be in the userform

Dim MyArray() As Variant

Call ReadArray(MyArray())

End Sub


Sub ReadArray(ByRef MyArray() As Variant)

ReDim MyArray(100)
MyArray(3) = 22
'code to fill array
End Sub



"WLMPilot" wrote:

Previously, we used a sheet (paper) to order our supplies, then key in what
was needed. In trying to go paperless, I have programmed excel for most of
what I need to do. However, I need help with the following:

I have a macro that will read data (list of items stocked and its max qty)
into an array when the user clicks the button "Begin Order". It gets the
data from a list on a worksheet in the same workbook. Then there will be a
userform that displays the list one item at a time.

The userform will display one item, and its max qty, at a time. The user
will enter the amount on hand and the macro will calculate amount needed to
order. Each time the user hits ENTER, the amount needed to order will be
entered in the form on the worksheet and the next item will display in the
userform.

How do I get the data, that is initially read into an array, to the userform
one at a time?

Thanks,
Les

There is always tradeoff that need to be made when you are desigining
software. I don't know all your requirements. If the data the stock items
keep on changing then yhou need to update the userform after the items get
changed. This can per done every time the userform is opened. It also
depends on how much time it takes to load the array if yo want to load the
array once or do it every time. And the amount of code it take to load the
array.

In this case there is a simple solution. The userform has an initialize
function that only gets run the first time the userform gets called. You ca
even call the initialize function when the workbook is opened. th
einitialize function can automtically shut down the user form after it is
initialized by perfoirming a HIDE.

"WLMPilot" wrote:

Thanks for your reply, but I have another questions concerning your response.

Trying to remember back for it has been a while. I am thinking that after
the ENTER button is clicked and the data is placed in the worksheet for that
stock item, then there is code to display (execute) the userform in order to
display it again with the next stock item.

If this is so, wouldn't options 2 and 3 read the data into the array
everytime instead of just once for the entire process?

Les

"Joel" wrote:

You have a few methods

1) Make the array public so the userform and the main code will both get the
same array
2) Put the code that put the data into the array in the userform code
3) Make the code that reads the worksheet a seperate subroutine that returns
the array like this

Sub Main() 'this code can be in the userform

Dim MyArray() As Variant

Call ReadArray(MyArray())

End Sub


Sub ReadArray(ByRef MyArray() As Variant)

ReDim MyArray(100)
MyArray(3) = 22
'code to fill array
End Sub



"WLMPilot" wrote:

Previously, we used a sheet (paper) to order our supplies, then key in what
was needed. In trying to go paperless, I have programmed excel for most of
what I need to do. However, I need help with the following:

I have a macro that will read data (list of items stocked and its max qty)
into an array when the user clicks the button "Begin Order". It gets the
data from a list on a worksheet in the same workbook. Then there will be a
userform that displays the list one item at a time.

The userform will display one item, and its max qty, at a time. The user
will enter the amount on hand and the macro will calculate amount needed to
order. Each time the user hits ENTER, the amount needed to order will be
entered in the form on the worksheet and the next item will display in the
userform.

How do I get the data, that is initially read into an array, to the userform
one at a time?

Thanks,
Les

Thanks for your help. I will research a little more to figure out the best
way.

Les

"Joel" wrote:

There is always tradeoff that need to be made when you are desigining
software. I don't know all your requirements. If the data the stock items
keep on changing then yhou need to update the userform after the items get
changed. This can per done every time the userform is opened. It also
depends on how much time it takes to load the array if yo want to load the
array once or do it every time. And the amount of code it take to load the
array.

In this case there is a simple solution. The userform has an initialize
function that only gets run the first time the userform gets called. You ca
even call the initialize function when the workbook is opened. th
einitialize function can automtically shut down the user form after it is
initialized by perfoirming a HIDE.

"WLMPilot" wrote:

Thanks for your reply, but I have another questions concerning your response.

Trying to remember back for it has been a while. I am thinking that after
the ENTER button is clicked and the data is placed in the worksheet for that
stock item, then there is code to display (execute) the userform in order to
display it again with the next stock item.

If this is so, wouldn't options 2 and 3 read the data into the array
everytime instead of just once for the entire process?

Les

"Joel" wrote:

You have a few methods

1) Make the array public so the userform and the main code will both get the
same array
2) Put the code that put the data into the array in the userform code
3) Make the code that reads the worksheet a seperate subroutine that returns
the array like this

Sub Main() 'this code can be in the userform

Dim MyArray() As Variant

Call ReadArray(MyArray())

End Sub


Sub ReadArray(ByRef MyArray() As Variant)

ReDim MyArray(100)
MyArray(3) = 22
'code to fill array
End Sub



"WLMPilot" wrote:

Previously, we used a sheet (paper) to order our supplies, then key in what
was needed. In trying to go paperless, I have programmed excel for most of
what I need to do. However, I need help with the following:

I have a macro that will read data (list of items stocked and its max qty)
into an array when the user clicks the button "Begin Order". It gets the
data from a list on a worksheet in the same workbook. Then there will be a
userform that displays the list one item at a time.

The userform will display one item, and its max qty, at a time. The user
will enter the amount on hand and the macro will calculate amount needed to
order. Each time the user hits ENTER, the amount needed to order will be
entered in the form on the worksheet and the next item will display in the
userform.

How do I get the data, that is initially read into an array, to the userform
one at a time?

Thanks,
Les