Hey Rick,

Sorry about not getting back sooner.

What you show for the original post does contain a few error. There
is only one InvCon_4 and there is no 3rd column code. So the
corrected code should be:

Case "I_"
Select Case (vNum - 42) Mod 7 +1
Case 10
InvCol_1 vName, vNum
Case 2
InvCol_2 vName, vNum
Case 4
InvCol_4 vName, vNum
Case 5
InvCol_5 vName, vNum
Case 6
InvCol_6 vName, vNum
Case 7
InvCol_7 vName, vNum
End Select

I messed up in my post, please accept my apology. I realize that
having the +1in the select case test is not significant, but it
matches up the case numbers with the procedure a bit better.

I am having a problem using Application.Run as in:

Case "I_"
Application.Run "InvCol_" & (1 + (vNum - 42) Mod 7)

I can't get it to work. VB cannot find the macro 'InvCol_1'. Which
is the correct procedure name for the first column. I even tried
running it with only Run "InvCol_" & (1 + (vNum - 42) Mod 7),
Removing the word Application made no difference. Homey suggested
moving the InvCol codes to a general module. So I tried that and
discovered that the Me.Controls is not available there.

It appears that if I cannot use a variable when calling the InvCol
procedures, Then I was forced back to the corrected original select
case solution.

Do you know of a way I can use your final solution? I really like the
way it looks.

If it makes any difference, I am running Excel 2003 on an XP box

Thanks for the assistance you have already given, I really do
appreciate it.

-Minitman
On Sun, 17 Jan 2010 19:34:19 -0500, "Rick Rothstein"
wrote:

You originally posted this...

Case "I_"
Select Case (vNum - 42) Mod 7
Case 0
InvCol_1 vName, vNum
Case 1
InvCol_2 vName, vNum
Case 2
InvCol_4 vName, vNum
Case 3
InvCol_4 vName, vNum
Case 4
InvCol_5 vName, vNum
Case 5
InvCol_6 vName, vNum
Case 6
InvCol_7 vName, vNum
End Select

I meant the following to replace all of it...

Case "I_"
Application.Run "InvCol_" & (1 + (vNum - 42) Mod 7)

You are showing an extra Select Case (for the vNum) which, if I understand
your set up, is not required (as a matter of fact, it would interfere with
the operation of the code I posted). If you need it, you can do the test for
iCol not being 3, but don't put it inside the extra Select Case block (the
one with Case 42 to 97). As I said, the code line I posted replaces all the
Select Case statements you showed originally... the only one you need is the
Case "I_" that you showed. Now, if there are other cases beside "I_", and I
assume there are, I can't say if the code line I posted can cover them or
not (mainly because you didn't show them to us), but the concept of using
the Application.Run can probably be used (you would just have to modify the
string representing the subroutine's name and any arguments the subroutine
required).