Excel

Create an "Easy" button

Ease of Use

Intermediate

Version tested with

2007 

Submitted by:

CHatmaker

Description:

Create a fun "Easy" button for invoking macros. 

Discussion:

Just for the fun of it, I provide an "Easy" button on spreadsheets where the user must enter information into a form - such as dates for a data extract. The user clicks the "Easy" button, it is attached to a macro, which in-turn displays the form. Yes - you could use a bit map instead. But this doesn't violate anyone's copyrighted image and besides, this is more fun for a coder ;) 

Code:

instructions for use

			

Sub Create_Easy_Button(x As Integer, Y As Integer, sMacro As String) With ActiveSheet.Shapes.AddShape(msoShapeOval, x, Y, 35, 35) .Name = "Easy_Button_Base" .Fill.ForeColor.RGB = RGB(200, 0, 0) 'Dark Red center .Placement = xlFreeFloating .OnAction = "Show_Prompt" With .Line 'White border .ForeColor.RGB = RGB(255, 255, 255) .Weight = 3 End With With .Shadow .Visible = True .OffsetX = 2 .OffsetY = 2 .Transparency = 0.5 .ForeColor.RGB = RGB(10, 10, 10) End With With .ThreeD .BevelTopType = 3 .BevelTopDepth = 20 'Rounded top .BevelTopInset = 19 'Rounded Top .ContourWidth = 0 'No line around the base .Depth = 2 .ExtrusionColorType = 1 .FieldOfView = 45 .LightAngle = 300 'Light from above and to the left .Perspective = 0 .PresetLighting = 15 .PresetMaterial = 6 'Plastic End With End With With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, x - 1, Y - 2, 35, 35) .Name = "Easy_Button_Text" With .TextFrame .MarginBottom = 0 .MarginLeft = 0 .MarginRight = 0 .MarginTop = 0 .HorizontalAlignment = xlHAlignCenter .VerticalAlignment = xlVAlignCenter .Characters.Text = "easy" With .Characters.Font .Bold = True .Size = 16 .Name = "Calibri" .Color = RGB(255, 255, 255) .Shadow = True End With End With .Line.Visible = False .Fill.Visible = False .TextEffect.PresetTextEffect = 2 .Placement = xlFreeFloating .OnAction = sMacro End With End Sub

How to use:

  1. Activate the worksheet where you want the button
  2. Open the VBE (Shift-F11)
  3. From the Immediate Window Type:
  4. Create_Easy_Button xHorizontalPosition, yVerticalPosition, sMacroToInvoke
  5. Switch back to the worksheet and enjoy your "Easy" button
  6. -or-
  7. call from a routine that sets up your spreadsheet
 

Test the code:

  1. Activate the worksheet where you want the button
  2. Open the VBE
  3. From the Immediate Window Type:
  4. Create_Easy_Button 8, 10, "frmPrompt"
  5. Switch back to the worksheet and enjoy your "Easy" button
  6. The sample contains slightly amended code to modify the size/shape of the button to suit the text length.
 

Sample File:

EasyButton.zip 19.54KB 

Approved by mdmackillop


This entry has been viewed 783 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express