We help IT Professionals succeed at work.

Creating a transparent CommandButton

RWayneH
RWayneH asked
on
I have a s-sheet that has 32 transparent buttons on it.  Problem is that I need to change the procedure that is run on the buttons.  When I record a macro I noticed that VBA is not declaring what button is being changed...

Is there a way to create a transparent CommandButton, so I can write a procedure that will change the script that is executed when it is clicked on?  then switch it back to what is was.

The recorded code is below...  I would rather have a CommandButton1, so I can select it point it to a different Sub, and then on a different day switch it back.  Looking for a bit of direction.

Please advise and thanks. -R-
Sub SwitchCompletedOrdersForAMondayOnTheDashboard()
'
' SwitchCompletedOrdersForAMondayOnTheDashboard Macro
'

'
    Sheets("Dashboard").Select
    Selection.OnAction = "MDL_GetOnlyPastDueCompletedOrdersTodayMinusThreeDay"
End Sub
Sub SwitchCompletedBackToTuesThruFriday()
'
' SwitchCompletedBackToTuesThruFriday Macro
'

'
    Range("A6").Select
    Selection.OnAction = "MDL_GetOnlyPastDueCompletedOrdersTodayMinusOneDay"
    Range("A5").Select
End Sub

Open in new window

Comment
Watch Question

Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
I accomplish similar with a file that I update on a weekly basis. The first update each week feeds the set of data into a fresh column, further updates throughout the day then amend this current set.

When I click my Copy Data, it shows a User form with 2 buttons "New" or "Update" clicking of which sets a parameter to 0 or 1; one being a New copy and 0 being an Update copy. This parameter is then used in subsequent macro to determine where the data goes.

User Form button Codes:

Private Sub NewCopy_Click()

    PerformCopy 1

End Sub

Private Sub UpdateCopy_Click()

    PerformCopy 0

End Sub

Open in new window


Subsequent code:

Sub PerformCopy(offset As Integer)

    CopyData.Hide
    Application.ScreenUpdating = False
    Application.Goto Reference:="Workings"
    Selection.Copy
    Range("A1").Select
    Application.Goto Reference:="Pigs"
    Selection.End(xlToRight).Select
    ActiveCell.offset(0, offset).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Application.CutCopyMode = False
    If offset = 1 Then Range("BI3") = Range("BI3") + 1
    Application.ScreenUpdating = True
    ActiveCell.Select

End Sub

Open in new window


Line 10 has the offset value set by the previous code.

I knwo this deosn't actually answer your question as such but it might point in the right direction for a way forward.
Thanks
Rob H

Commented:
Yes, there is a way to make it transparent .
Check the following worksheet and pic. Let me know if this is what you are looking for.
Transparent.bmp
Transparent-menu.xlsm

Author

Commented:
Mine is simular, but I need to piece it together... first before I create the userform....  I will get there, but I first need to create a transparent CommandButton instead of the button that I am currently using...

I am running an update from a previous days data and comparing it.  However on a Monday in need to offset to Friday, on Tuesday I offset to Monday, and holiday weekends is another set of code.  4 day off for a 3 day weekend and 5 off a 4 day... etc.

The goal is reassign what code is run on a transparent Command Button... (chg the sub it is pointing too)  I need to be able to select CommandButton5, switch the sub.  These transparent button are setting on top of cells that display a number.  when you click on the number, the transparent button will run and the detailed records are shown.

-R-

Author

Commented:
the sample file command button is blue....  It needs to be clear, no color...  users will not know they are there.  Height and width will be exactly the same size as a cell on the s-sheet.

Can you make the same button with no color?  Please let me know what cell it is in so I can find it.   Thanks.  -R-

Author

Commented:
I think I have the button now...  CommandButton1   click it in the new sample file.  Now all I need is the VBA and will switch the sub that is run.  Plan is to do this for a half dozen of them and to be able to switch them back and forth...  -R-
Transparent-menu2.xlsm

Author

Commented:
an even further example in this sample file.

All I need for right now, is the VBA that will, switch the code run by CommandButton1.  I have a userform that will select which day of the week it is or if it is a holiday or extended holiday weekend.  There are a number of buttons and I only want that selected once.  I will then write a larger procedure to switch the necessary buttons all at once based on this example. -R-
Transparent-menu3.xlsm

Commented:
right, use formula like =today() =weekday() to find out today's date..and use vba select case statement to switch code.

here is an example:
http://www.youtube.com/watch?v=_hnYzc0beqQ
Transparent3.xlsm

Author

Commented:
Could I get the VBA for this?  In English.

Sub ChangeCommandButton1_ToToday

Select CommandButton1
Chg it to Call today

End Sub

Sub ChangeCommandButton1_ToWeekday

Select CommandButton1
Chg it to Call weekday

End Sub

Author

Commented:
I am not following the sample w/ the blue square in it....  those are not CommandButtons.  I am not sure that you can change the assign macro to the non commandbuttons?  How would you declare which of the buttons you are chg'ing?  that is why I would like to start using CommandButtons so I can declare which one I want to change and to what is assigned to it.  -R-

Commented:
Please review this code and let me know if it work.
Private Sub CommandButton1_Click()
Dim val As String
val = Range("B2").Value
dif = Range("B7").Value
Select Case val
    Case "2"
    Call CodeForMondays
    'MsgBox "this is Monday"
    Case "5" To "6"
    If dif = 2 Then
    Call CodeForTuesdaythruFriday
    'MsgBox "this is thursday to friday"
    Else
    Call CodeForHolidayWeekends
    End If
End Select
End Sub

Open in new window

Transparent-menu4.xlsm

Author

Commented:
This is a good solution and thanks.  Is there a way to just flat out chg the Call and not programically chg it based on the day?  There are times when an extended weekend, 3 and 4 day holidays happen, that I need to select manually what Call I need for a certain scenario.  For example Thanksgiving is coming up and most will get Thurday and Friday off, so come Monday it needs to look back as far as Wednesday of the previous week.

I am going to call a userform up that has the four buttons on it, so the user determines what day of the week it is.  1. The Monday after a 2 day weekend.  2. The Monday after a 3 day weekend. 3. The Monday after a 4 day weekend. or 4. A Tueday-thru-Friday, when it just need to out one day back.

That said, how do I chg the code so it simple edits the Call tag in the CommandButton only, and is not based on a date input or cell.  I need to chg this manually and will do this individually base on which of the four buttons determine the number of day that I need to look back. (I will write the userform for that).  I just need to be able to chg the Call tag for a given CommandButton.

Hope this makes sense. -R-

Author

Commented:
I guess another way to request this would be.

How do I change the CommandButton Call procedure, without clicking on it.  I know that this could be done with an if statement, but that is not what is needed here, nor the request.  I will get to that later. (if needed at all),  

In English:

Sub ChangeCommandButton1_ToMondays
'There is only two lines of code in this

Select CommandButton1
Call CodeForMondays  'chg the call to: CodeForMondays from what ever it was

End Sub

Sub ChangeCommandButton1_ToWeekdays

Select CommandButton1
Call CodeForWeekdays  'chg the call to: CodeForMondays from what ever it was

End Sub

Commented:
Ok, Please review the attached worksheet.
You could click the button and it will call the menu that has 4 buttons.
Please let me know if this work for you.
Transparent-menu5.xlsm

Author

Commented:
Not really... this would mean that would have to answer the userform for every button?  The goal is to chg the call tag for the CommandButton without having to click on it, so that when the user does click on it, the right one is executed.

I am chg'ing numerous buttons and am looking for an example to use for them all.  I just need one to work that I will apply to all....  I do not want to be asked to do this everyttime a button is used.  It will have the proper tag already.  Once I can get the code that chg's the Call tag for CommandbuttonX.  I am going to use this to chg all the buttons at once.  

It has to chg the call tag without clicking on the CommandButton...  An example for Monday would be, Select button1 chg call tag to DS_Monday, buttom2, chg call tag to MDL_Monday, button3, chg to MM_Monday.  etc.    On Tuesday all those call tags would chg to Tuesday....  and then again for whatever button is selected in the userform you wrote.

Yes I will use a simular userform to declare which buttons need to be changed, but I still need to code to chg a CommandButton call tag, without clicking on it.  Remember there are 30+ transparent buttons in the sheet...  

Can this even be done?  See the "In English" posted in previous post...  that is all that is needed if it can be done without clicking on any buttons and executing it from a Sub.

hope this makes sense.   -R-




Commented:
You can call the CommandButton Call procedure, without clicking on the button but open the worksheet.  see code 1

You can call the CommandButton Call procedure, without clicking on the button but open the workbook. see code 2

If you want to add a procedure or delete a procedure to a module, you can use VBA Extensibility.
Example: http://www.cpearson.com/excel/vbe.aspx

My point is that you have to do something to trigger the change. I used select, if, and some parameters to facilitate those change. It is not likely to change the vba without action like clicking or opening.
Private Sub Worksheet_Activate()
MsgBox " Sub for Mondays "
End Sub

Open in new window

Private Sub Workbook_Open()
Call CodeForMondays
End Sub

Open in new window

Commented:
By the way, to access the VBIDE you need to make the workbook as a trusted workbook like the picture.
Trust-Worksheet.JPG

Author

Commented:
So the way I am understanding this, there is no way to change the way a series of buttons work, without having something to trigger it.  If I answered the userform question once...  and stored a value , then reference that value in each CommandButton, so it knows which call tag to execute.

Is this the way your sample is set up?  and I just missed it?  I think I could do this, and just edit the script in each CommandButton to point to the selected value from the userform.

Using your userform it set the value,  how would the CommandButtonX code look that referenced back to what was selected in the userform?

I think we are getting closer, and it is making more sense to me.  -R-

Commented:
Back to your original question. You want to macro that will change the vba script  that is executed when it is clicked on.
You will need the VBIDE.
Check the link that I posted: http://www.cpearson.com/excel/vbe.aspx
You need to go to tool/reference and click the "Microsoft visual basic for applications extensibility X.X"
Check the code about adding and delete in the post. There are examples, you can just copy those examples and test.
You will also need to make the sure the worksheet is trusted like the above post.

You need some action to change the code. for example, you can open the worksheet or open the workbook. Some how you will need to code to know what to change to. I used the select and if. You can use something else that will identify the date.

Commented:
Right, you need something to trigger the change. Yes, you can answered the user form question one and you can reference that value in each commandbutton.

To reference the value, you can use userform2.textbox1.value
Sub abc()

msgbox UserForm2.TextBox1.Value

end sub

Open in new window

Transparent-menu5.xlsm

Author

Commented:
This is it!!  Not to be picky, but can we clean this up?  It is running the same call tag (MsgBox) for each number input...  I thought the MsgBox would change for each number entered,  and it is flashing the result for some reason...  but I believe we have our solution  YES!!.

I am still a little confused on how to store that value in the transparent button.   I can call the userform2 up from a different procedure.... to set the value only.  Now inside the transparent button I need to reference all four values and execute the proper one based on the value.  We need to rewrite the codes in the transparent button...  I other words call the userform up separate from the transparent one.    -R-

Commented:
Yes, I re-write the vba and the msgbox will change for each number entered.
You might want to study the code to see how it work.

You can store the value to the worksheet and move the button above to cover the value.
You can also make H1 font with white color so people won't see it.

Worksheets("sheet1").Range("H1").Value = UserForm2.TextBox1.Value

check the following example.
Private Sub CommandButton1_Click()
If UserForm2.TextBox1.Value = 1 Then
Call CodeForMondays
ElseIf UserForm2.TextBox1.Value = 2 Then
Call CodeForTuesdaythruFriday
ElseIf UserForm2.TextBox1.Value = 3 Then
Call CodeForHolidayWeekends
ElseIf UserForm2.TextBox1.Value = 4 Then
Call CodeForExtendedHolidayWeekends
Else
End If
End Sub

Open in new window

Transparent-menu6.xlsm

Author

Commented:
Please see the new sample file.  What I need is to click a separate button and get the value...  then goto the transparent button and get the MsgBox for whatever was selected...

The re-displaying of the value in a msgbox is not needed, and we can take the CommandButton out of the userform2.  All userform2 should do is get a value.  In the transparent button is where a nested if statement needs to be, so it calls up the right, msgbox....  I tried putting the code you supplied in the tranparent button and it is not working.  How do we transter the value of userform2 to the code in the tranparent button?  We are so close...

The plan is to later replace these msgbox results with the approperiate call tags...   -R-
Sample7.xlsm
Commented:
1) click to select day --> select value
2) the value will save to the worksheet at cell C1
3) click the transparent button and it will read the value in cell c1 and run the macro accordingly.
Sample8.xlsm

Author

Commented:
sorry for the delay reply, I was testing sample 8.  This will work!!  EXCELent!!  Thank you Thank you Thank you!!  The only thing that would make this perfect would be, if there is a way to remove the edge borders of the transparent button.  Seems that all the clear background settings in the button properities have some sort of edge on them.

I appreciate the help on this.  -R-

Author

Commented:
As I said EXCELent!!!  Great solution.  -R-