Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create button code stops working
I have a template with two sheets (MASTER.. codename Sheet1, and
Summary.....codename Sheet3). There are no general modules, and the ThisWorkbook module is empty. The only code is in Sheet1. On the Master sheet is a CommandButton from the Controls toolbox, which allows a new sheet to be added and formatted according to ranges in the Master. It also creates 3 new buttons (from the Forms toolbar) on the new sheet, and assigns separate subs in the Master sheet code module to each button......and it's the assign statement that is failing. Here is the relevent Sheet1 code: Private Sub Cb_NewSheet_Click() Dim strSheetName As String, strNoOfPages As Integer Dim i As Integer, pasteRng As Range, ws As Worksheet Application.ScreenUpdating = False strSheetName = Application.InputBox _ ("Please enter a name for the new sheet", Type:=2) If strSheetName = "" Then MsgBox ("You must enter a name for the sheet") Exit Sub End If strNoOfPages = Application.InputBox _ ("How many pages do you need?", Type:=1) If strNoOfPages = False Then MsgBox ("You must specify how many pages") Exit Sub End If Worksheets.Add Befo=Sheets("Summary") ActiveSheet.Name = strSheetName ' code then follows to format the new sheet, create the ' required number of pages, and then call the button sub: Call Create3Buttons 'which leads to: Private Sub Create3Buttons() Dim btn As Button, ws As Worksheet Set ws = ActiveSheet With ws Set btn = ws.Buttons.Add(550, 60, 100, 15) btn.Select Selection.Characters.Text = "Add a Page" With Selection .Font.Name = "Arial" .Font.FontStyle = "Regular" .Font.Size = 10 .Font.ColorIndex = xlAutomatic .Locked = True .LockedText = True End With btn.OnAction = ThisWorkbook.Name & _ "!Sheet1.General_Button1_click" Set btn = ws.Buttons.Add(550, 80, 100, 15) btn.Select Selection.Characters.Text = "Show Page Heights" With Selection .Font.Name = "Arial" .Font.FontStyle = "Regular" .Font.Size = 10 .Font.ColorIndex = xlAutomatic .Locked = True .LockedText = True End With btn.OnAction = ThisWorkbook.Name & _ "!Sheet1.General_Button2_click" Set btn = ws.Buttons.Add(550, 100, 100, 15) btn.Select Selection.Characters.Text = "Hide Page Heights" With Selection .Font.Name = "Arial" .Font.FontStyle = "Regular" .Font.Size = 10 .Font.ColorIndex = xlAutomatic .Locked = True .LockedText = True End With btn.OnAction = ThisWorkbook.Name & _ "!Sheet1.General_Button3_click" .Range("A1").Select End With End Sub The btn.OnAction statement fails with the message "Unable to set the OnAction property of the Button class". The same statement has worked in other books. Why might it now have stopped, please? Regards. ' and the the button code: |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Option Button code not working | Excel Discussion (Misc queries) | |||
VB Code stops working | Excel Programming | |||
Why does this code not create a button when called??? | Excel Programming | |||
Old button code not working in Excel2002 | Excel Programming | |||
Create Command Button from Code | Excel Programming |