Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Use of Variable Customized Button in Excel/VB6...Need Help. 1

Status
Not open for further replies.
marcdibi

marcdibi

Technical User
Mar 18, 2008
12
US
Hello,

I need help for the following item: I have 18 customized buttons in Excel with their macros in VB6 and once I click on one of those buttons, I want to remmber this action in another button. For instance, if I click on button A, the 'variable' button will do the same action as button A each time I click on it. Now if I click on button B, the 'variable' button will do the same action as button B each time I click on it. Etc.. keep remembering the latest button (among those 18) hit and each time I click on it, repeat the same action recorded.

Below is two samples of the macros for buttons A and B:

Code: 
Sub A()

Close #5
[COLOR=green]' Open Filename For Output As 5
[/color]
Filename = Sheet7.Cells(1, 7).Text
Open Filename For Output As 5

StartRow = 26
StartCol = 14
EndRow = 65
EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet4.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i
Close #5

End Sub

Sub B()

Close #5
[COLOR=green]' Open Filename For Output As 5 [/color]
Filename = Sheet7.Cells(1, 7).Text
Open Filename For Output As 5

StartRow = 69
StartCol = 14
EndRow = 105
EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet4.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i
Close #5

End Sub

To avoid any confusion, I want to track the name of the variable button as either the name of the latest customized button hit or the name of his previous macro.

It's tough. Thanks for your time, I would appreciate.
Regards,
Marc Dibi
 
Sort by date Sort by votes
You may use a global variable.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote

Looks to me that both Subs are the same, and I am affraid you have 18 of them.

You could do:
Code: 
Sub MySub([blue]strLetter[/blue] As String)

Close #5
[green]' Open Filename For Output As 5 [/green]
Filename = Sheet7.Cells(1, 7).Text
Open Filename For Output As 5

Select Case [blue]strLetter[/blue]
    Case "A"
        StartRow = 26
        StartCol = 14
        EndRow = 65
        EndCol = 24

   Case "B"
        StartRow = 69
        StartCol = 14
        EndRow = 105
        EndCol = 24

End Select

For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet4.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i
Close #5

End Sub

and this way you just need to keep one letter in, let's say, Global Variable like PHV suggested.

Have fun.

---- Andy
 
Upvote 0 Downvote
Is it possible to specify in VB6 that clicking on a customized button "A" in Excel will set the Global Variable strLetter to "A"? Then to assign this strLetter as the name of a 'variable' button? X(|)X
Let say button "A" is in custom toolbar ANALYSIS and the
'variable' button is in custom toolbar EASY: I want ANALYSIS."button A".name=strLetter. Then Easy."button"=strLetter.

How would you assigned the macro to the 'variable' button?
I can't make it show up in the Assign Macro box of Excel.

Code: 
[COLOR=green]'declaration[/color]
Public strLetter As String

Code: 
Sub MySub(strLetter)
.
.
.

Does that make sense?
 
Upvote 0 Downvote

You cannot do it this way.

Your Public var strLetter has different scope (it is Public), and your strLetter in your Sub is (very) local, which means it is decalred and available only in this Sub.

If you are going to declare your strLetter in the Module, skip it in your Sub, ie.

Sub MaySub()

This way you can assign value to strLetter anywhere in your code, and you can read it anywhere in your code.

I would suggest the name of gstrLetter (global string name) so in your code, just by looking at the name of your var, you will know the scope of it.

And yes, you can use it in your code:
instead of

.Range("[blue]A[/blue]2:[blue]A[/blue]7").Select

you can do

.Range([blue]gstrLetter[/blue] & "2:" & [blue]gstrLetter[/blue] & "7").Select


Have fun.

---- Andy
 
Upvote 0 Downvote
What about storing the button clicked in global object variable and set its value to True by the 'variable' button event procedure?

combo
 
Upvote 0 Downvote
Andrzejek,

The MySub() macro show up now in the Assign Macro box of Excel to be assign to the 'variable' button.

I don't get what is "2" and "7" in the code:
Code: 
.Range([blue]gstrLetter [/blue]& "2:" & [blue]gstrLetter [/blue] & "7").Select

Combo do you have an example for your idea?

Let's start from the beginning: I have extensive crystal filter design software that I am linking to Eagleware for analysis. Everything works nice.

I think Andrzejek believes that my buttons are actually the colons in Excel: A, B, .... I'm afraid I was not clear enough. Basically, consider A, B ... like File-->Open or Edit-->Cut..., mine are a customized toolbar name ANALYSIS with several menus inside and buttons :
Code: 
[i]This is not a code, just an explanation[/i]
*ANALYSIS*
		*2-Pole*	-->	*NB*	-->	*NBL 1S MDR*.
						-->	*NBH 1S SMR*.
						-->	INBL 1S MDR*.
				-->	*MinZ*	-->	*MZL 1S MDR*.
						-->	*MZH 1S SMR*.
						-->	*MZH 2S SMR*.

		*4-Pole*	-->	*NB*	-->	*NB 2S MDR*.
						-->	*INB 2S MDR*.						
						-->	*NBH 2S SMR*.
				-->	*MinZ*	-->	*MZL 2S MDR*.
						-->	*MZH 2S SMR*.

		*6-Pole*-->	*NB*	-->	NBL_3S_ MDR
					-->	NB_3S_SMR
			-->	*MinZ*	-->	MZL_3S_MDR

Those buttons do the following:
[ol]
[li]create a netlist (CKT) File[/li]
[li]check if Genesys application already exists. If YES, close the OLD Genesys window and open a NEW one. If NOT, create a NEW one, with KillProcess(), [/li]
[/ol]

Actually you're right the buttons are very similar, except for the sheet where they take their data: 6 macros --> sheet4, 5 macros --> sheet8, 3 macros --> sheet9.

Below is the full code for one macro:
Code: 
[COLOR=green]
'
' Writing Design Analysis Parameters in FileName.
'
[/color]
Sub Analysis_2_Pole_NBL_1S_MDR()
[COLOR=green]
'
' Macro created 03/12/2008 by Marc Dibi
'
[/color]
Close #5
[COLOR=green]' Open Filename For Output As 5 [/color]
Filename = Sheet7.Cells(1, 7).Text
Open Filename For Output As 5

StartRow = 26
StartCol = 14
EndRow = 65
EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet4.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i
Close #5

KillProcess ("Genesys.exe") [COLOR=green]' If Genesys application exits, Close it [/color]

Dim dTaskID As Double, path As String

path = "C:\Program Files\GENESYS2008.01\Bin\Genesys.exe"
Filename2 = Sheet7.Cells(1, 12).Text

[COLOR=green]' Execute Filename2 Using Path [/color]
dTaskID = Shell(path + " " + Filename2, vbNormalNoFocus)

End Sub

[B][I]To get the KillProcess() pgm, visit thread707-1459236 [/I][/B]

Now here is where I need help:

[ul]
[li]Once I choose (click) which Design I'm about to work with in the ANALYSIS toolbar, I just want to click on the same 'variable' button which would be equivalent in action to the one selected previously in the ANALYSIS menu. Basically like a short-cut or an image, but flexible. [/li]
[li]Get the name of the macro or button previously selected in ANALYSIS toolbar and show it up in the 'variable' button.[/li]
[/ul]

The first code of Andrzejek can be modified for sheet 4, 8 and 9 by making the For i...For j selection inside each case.

I appreciate your support guys. I know it's possible and I need your help to bit it!

Regards,
Marc
 
Upvote 0 Downvote
I believe those buttons would be easier to affect in the 'variable' button, for different reasons:
Code: 
[B][I]This is not a code, just an explanation [/I][/B]
*ANALYSIS*
	*2-Pole_NBL_1S_MDR*
	*2-Pole_NBH_1S_SMR*
	*2-Pole_INBL_1S_MDR*
	*2-Pole_MZL_1S_MDR*
	*2-Pole_MZH_1S_SMR*
	*2-Pole_MZH_2S_SMR*
	*4-Pole_NB_2S_MDR*
	*4-Pole_INB_2S_MDR*
	*4-Pole_NBH_2S_SMR*
	*4-Pole_MZL_2S_MDR*
	*4-Pole_MZH_2S_SMR*
	*6-Pole_NBL_3S_MDR*
	*6-Pole_NB_3S_SMR*
	*6-Pole_MZL_3S_MDR*

Marc
 
Upvote 0 Downvote
How do I specify gstrLetter as a variable passed into Mysub()?

Code: 
Public gstrLetter As String

Code: 
Sub MySub()
'
' Macro created 03/20/2008 by Marc Dibi
'
Close #5
' Open Filename For Output As 5
Filename = Sheet7.Cells(1, 7).Text
Open Filename For Output As 5

Select Case gstrLetter

    Case "2-Pole_NBL_1S_MDR"
        StartRow = 26
        StartCol = 14
        EndRow = 65
        EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet4.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i

   Case "2-Pole_NBH_1S_SMR"
        StartRow = 69
        StartCol = 14
        EndRow = 105
        EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet4.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i

    Case "2-Pole_INBL_1S_MDR"
    StartRow = 107
    StartCol = 14
    EndRow = 146
    EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet4.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i

    Case "2-Pole_MZL_1S_MDR"
    StartRow = 149
    StartCol = 14
    EndRow = 186
    EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet4.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i

    Case "2-Pole_MZH_1S_SMR"
    StartRow = 192
    StartCol = 14
    EndRow = 222
    EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet4.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i

    Case "2-Pole_MZH_2S_SMR"
    StartRow = 230
    StartCol = 14
    EndRow = 264
    EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet4.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i

    Case "4-Pole_NB_2S_MDR"
    StartRow = 30
    StartCol = 14
    EndRow = 59
    EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet8.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i

  Case "4-Pole_INB_2S_MDR"
    StartRow = 74
    StartCol = 14
    EndRow = 103
    EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet8.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i

  Case "4-Pole_NBH_2S_SMR"
    StartRow = 164
    StartCol = 14
    EndRow = 212
    EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet8.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i

    Case "4-Pole_MZL_2S_MDR"
    StartRow = 120
    StartCol = 14
    EndRow = 151
    EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet8.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i

    Case "4-Pole_MZH_2S_SMR"
    StartRow = 219
    StartCol = 14
    EndRow = 271
    EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet8.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i

    Case "6-Pole_NBL_3S_MDR"
    StartRow = 33
    StartCol = 14
    EndRow = 73
    EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet9.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i

    Case "6-Pole_NB_3S_SMR"
    StartRow = 158
    StartCol = 14
    EndRow = 206
    EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet9.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i

    Case "6-Pole_MZL_3S_MDR"
    StartRow = 99
    StartCol = 14
    EndRow = 143
    EndCol = 24
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet9.Cells(i, j).Text; "      ";
    Next j
Print #5, " "
Next i

End Select

Close #5

KillProcess ("Genesys.exe") ' If Genesys application exits, Close it

Dim dTaskID As Double, path As String

path = "C:\Program Files\GENESYS2008.01\Bin\Genesys.exe"
Filename2 = Sheet7.Cells(1, 12).Text

' Execute Filename2 Using Path
dTaskID = Shell(path + " " + Filename2, vbNormalNoFocus)

End Sub

Thanks.
Marc.
 
Upvote 0 Downvote
I assumed you have ms forms commandbuttons. If no (forms button) then Application.Caller can be used to find and store the button's name and pass it as parameter.
Code: 
Public xButton As MSForms.CommandButton
Code: 
Private Sub CommandButton1_Click()
Set xButton = Me.CommandButton1
MsgBox "click 1"
End Sub

Private Sub CommandButton2_Click()
Set xButton = Me.CommandButton2
MsgBox "click 2"
End Sub

Private Sub CommandButtonX_Click()
xButton = True
End Sub


combo
 
Upvote 0 Downvote

marcdibi
How do I specify gstrLetter as a variable passed into Mysub()?
Click to expand...
You don't.
If you assign the value to gstrLetter anywhere in your code, it will be available in your MySub, you don't have to do anything else.

So if anywhere in the code you will say:
Code: 
gstrLetter = "2-Pole_NBL_1S_MDR"
your MySub will pick it up.

And looks to me that this piece of code:
Code: 
For i = StartRow To EndRow
    For j = StartCol To EndCol
    Print #5, Sheet9.Cells(i, j).Text; "      ";
Next j
Print #5, " "
is in all Cases in your Select Case.
If it is true, move it outside your Select Case statement and mentioned it just once.

PS. Forget this little .Range("A2:A7") stuff, I thought you just want to keep one letter in your gstrLetter variable.

Have fun.

---- Andy
 
Upvote 0 Downvote
Combo,

I am working on using your idea to set the name of the 'variable' button. Da, I'm confused...

Andrzejek, I have fun. I assigned this code
Code: 
gstrLetter = "2-Pole_NBL_1S_MDR"
in his specific macro. So when I click first on *ANALYSIS*
*2-Pole_NBL_1S_MDR*, my 'variable' button associated to MySub() pick it up. Bingo! It's working. Thanks!

By the way,
Code: 
Print #5, Sheet9.Cells(i, j).Text; "      ";
is not in all Cases in my Select Case.
I have
Code: 
Print #5, [b]Sheet4[/b].Cells(i, j).Text; "      ";
.
.
.
Print #5, [b]Sheet8[/b].Cells(i, j).Text; "      ";
.
.
.
Print #5, [b]Sheet9[/b].Cells(i, j).Text; "      ";
.
.
.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
731
strongm
strongm
SLW89
  • Locked
  • Question
VBA newbie needs help
Replies
4
Views
465
Andrzejek
Andrzejek
huiettcm
  • Locked
  • Question
Excel UserForm Window 'Focus' Event Listener 1
Replies
14
Views
706
huiettcm
huiettcm
thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
529
combo
combo
JAB_123
  • Locked
  • Question
I want to use an excel file for inp
Replies
3
Views
374
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top