PDA

View Full Version : Create Button Shadow Effect



zoom38
08-11-2014, 10:52 AM
Hello, I am trying to create a shadow effect on a button I am creating using vba and have tried for hours with no success. Here are the two versions of code that I have been using to do this.



If ActiveSheet.Name = "204 (2)" Then
ActiveSheet.Buttons.Add(628, 48, 72, 26).Select
Selection.Name = "Remove 204"
Selection.Font.Bold = True
Selection.OnAction = "Remove_204"
ActiveSheet.Shapes("Remove 204").Select
Selection.Characters.Text = "Remove 204"
' Selection.Shadow = True
End If





If ActiveSheet.Name = "204 (2)" Then
Set btn = ActiveSheet.Buttons.Add(628, 48, 72, 26)
With btn
.OnAction = "Remove_204"
.Caption = "Remove 204"
.Font.Size = 11
.Font.Bold = True
' .Shadow = True
End With
End If


I can't figure it out using either method. Where have I gone wrong?

Gary

snb
08-11-2014, 11:26 AM
Sub M_snb()
Sheet1.OLEObjects.Add("Forms.CommandButton.1", , , , , , , 200, 100, 100, 35).Shadow = True
End Sub

zoom38
08-11-2014, 12:01 PM
With that I am now having trouble setting the other properties such as
OnAction = "Remove_204"
Caption = "Remove 204"
Font.Size = 11
Font.Bold = True

Can you give me a push.

Thanks

snb
08-11-2014, 03:34 PM
Read the VBeditor helpfiles to get acquainted with the difference between ActiveX controls and formcontrols)

zoom38
08-12-2014, 10:36 AM
Read the VBeditor helpfiles to get acquainted with the difference between ActiveX controls and formcontrols)


I spent hours yesterday and today trying to figure it out using the help files and searching the net with no success. I would use the macro recorder but I can't find the "Assign Macro" function anywhere.

Can you help me with this?

thx
Gary

zoom38
08-12-2014, 12:34 PM
So here is where i'm at. I have code in a Module that creates a new worksheet and also creates a button on that new worksheet. In the code I would like to have a shadow effect applied to the button and a macro assigned to the button. I have not been able to write code that will create the button shadow effect and assign a macro. I have only been able to create code that does one of the two requirements. In trying snb's code I am not able to assign a macro. I've been at this for two days and am very frustrated. Where am I going wrong?
Please help.

snb
08-12-2014, 01:50 PM
Start with:

- creating a userform
- adding a commdnbutton in that userform
- adding code in the eventcode of that commandbutton.

You will find lots of insttuctions to do that.

If you create an ActiveX commandbutton in a worksheet, doubleclick it and you will be able to insert any code you like.

If you do not understand these instruction: start with the basics in VBA first (e.g VBA for Dummies, written by J. Walkenbach)

zoom38
08-12-2014, 02:33 PM
snd, I'm not familiar with user forms so that's why I didn't try that. If a user form creates a pop up form, that is not what I was looking for. I was looking for a simple button with the shadow effect and macro assigned to it. Yes I could easily double click the commandbutton on the worksheet and add the code, but then what do the other users do after they press a different button to create the worksheet and are not familiar with VBA? If the button was to be permanent, I'm sure I would have figured it out by now but since the sheets are regularly created and deleted (by code I have written) I have not been able to do as previously discussed. I have been all over the net, through the excel help backwards and forwards, searched all over multiple bulletin boards (i'm sure far more in depth than VBA for dummies, but thanks for the advice) and have tried hundreds of code variations. I may not be a "Master" and not too familiar with user controls but I am familiar with VBA in general. I asked the question here because I couldn't figure it out and needed help. In my initial post I explain that I was looking for assistance in creating the shadow effect on the button after creating it. Your assistance led me to create the shadow effect but then prevented me from assigning a macro. I'm sure its a simple piece that I am missing, maybe not, nobody else chimed in with a solution, but again I wouldn't be here wasting your time if I could figure it out.