Hi guys,
I have written some <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> code for a quotation calculator. This was my first time using macro and <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym>, so it was quite a challenge. After two weeks I got it to work perfectly on my MAC, but once I sent it out for my team to test, I realise that the macros do not work on some newer versions of Excel.
Can someone please help me to fix this and get it to work on all Excel versions?
Firstly, I have a start sheet asking people to enable macros, and this disappears once they do so. It also includes a code that can protect and unprotect the worksheets. These codes seems to work on all Excel versions:
Next I have code that when a button is pressed, a new table and button is created. This works on Excel for MAC 2011 and Excel for Windows 2007, but not any newer versions... In Excel 2013, when the button is clicked, it says, "Run-time error '1004': Unable to get the buttons property of the Worksheet class".
This is the code and I have underlined where is gets stuck when I press debug:
Automatically Sheet 3 is highlighted on the left hand side under Microsoft Excel Objects, even though the active sheet is Sheet 1. If I close the debugger, I am automatically on Sheet 3 (which has no buttons and no macros as of yet). If I go back to Sheet 1 and press the button a second time it works??? It creates the table and new button. If I press it a third time, it does not work and throws up the same error and takes me to sheet 3. If I press it a forth time it works again, and so on?
I have tried to replace the 'ActiveSheet' with 'Sheet 1' and I get this new error, "Run-time error '1004': Method 'Range' of object'_Worksheet' failed". Now it goes all the way through to my Add Button sub, and gets stuck here:
It once again highlights Sheet 3 in the debugger, and upon closing the debugger, I am once again on Sheet 3. If I click on Sheet 1 again and press the button it works. Once again it works on even number of clicks, but not on odd number of clicks???
I have tried deleting all other worksheets except for Sheet 1 and the start 'Enable Macros' sheet. But I get the same errors. The only difference being that it doesn't take me to Sheet 3 upon closing the debugger.
I have even tried replacing the button, by creating a new button in Excel 2013. But nothing seems to solve the problem.
Now if you click on the newly created, by the above code, Add Wall button, on the first click it DOES WORK and adds a new row to the table. But on the second click it throws up this error, "Run-time error '-2147024809 (80070057)': The item with the specified name wasn't found". The forth time it works, and the fifth it doesn't, and so on... Here is the code and the point at which it gets stuck is underlined:
If I change 'ActiveSheet' to 'Sheet1' in this part of the code, I can click the button, no errors appear, the screen moves for a second, but in the end nothing happens?
I have tried adding in error handling, and this just stops the run-time error pop up from showing up, but the buttons only ever work every second click, which still is not good enough.
Please forgive me if I haven't written my code in the correct format, I am just not sure how to???
Please can someone help me solve this ASAP!
Thank you!
I have written some <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> code for a quotation calculator. This was my first time using macro and <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym>, so it was quite a challenge. After two weeks I got it to work perfectly on my MAC, but once I sent it out for my team to test, I realise that the macros do not work on some newer versions of Excel.
Can someone please help me to fix this and get it to work on all Excel versions?
Firstly, I have a start sheet asking people to enable macros, and this disappears once they do so. It also includes a code that can protect and unprotect the worksheets. These codes seems to work on all Excel versions:
Code:
Private Sub Workbook_Open()
Call UnprotectBook
'Step 1: Declare your variables
Dim ws As Worksheet
'Step 2: Start looping through all worksheets
For Each ws In ThisWorkbook.Worksheets
'Step 3: Unhide All Worksheets
ws.Visible = xlSheetVisible
'Step 4: Loop to next worksheet
Next ws
'Step 5: Hide the Start Sheet
Sheets("Start").Visible = xlVeryHidden
Call ProtectBook
End Sub
Sub UnprotectAll()
Dim sh As Worksheet
Dim yourPassword As String
yourPassword = "xxx"
For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect Password:=yourPassword
Next sh
End Sub
Sub ProtectAll()
Dim sh As Worksheet
Dim yourPassword As String
yourPassword = "xxx"
For Each sh In ActiveWorkbook.Worksheets
sh.Protect Password:=yourPassword
Next sh
End SubThis is the code and I have underlined where is gets stuck when I press debug:
Code:
Sub Add_Section()
'
' Macro to add section
Application.ScreenUpdating = False
Call UnprotectAll
Dim row As Long
row = ActiveSheet.Buttons("Add_Section").TopLeftCell.row
Rows("13:22").Select
Selection.Copy
Rows(row - 1).Select
Selection.Insert Shift:=xlDown
Call AddSection4
Application.ScreenUpdating = True
End Sub
Sub AddSection4()
'
' Macro to add section4
'
Application.ScreenUpdating = False
Dim row As Long
row = ActiveSheet.Buttons("Add_Section").TopLeftCell.row
Cells(row - 10, 3).Formula = Range("C14").Value + 1
Call IncreaseValue
Application.ScreenUpdating = True
End Sub
Sub IncreaseValue()
Application.ScreenUpdating = False
Range("C14").Value = Range("C14").Value + 1
Call AddSection5
Application.ScreenUpdating = True
End Sub
Sub AddSection5()
'
' Macro to add section5
Application.ScreenUpdating = False
Dim row As Long
row = ActiveSheet.Buttons("Add_Section").TopLeftCell.row
Cells(row - 3, 3).Formula = "=ROUNDUP((" & Cells(row - 4, 3).Address & "/C10),0)"
Cells(row - 2, 3).Formula = "=((" & Cells(row - 3, 3).Address & "*C9*C10)*0.000001)"
Cells(row - 4, 3).Formula = "=SUM(" & Cells(row - 8, 3).Address & ":" & Cells(row - 5, 3).Address & ")"
Rows(row - 1).Insert
Call Add_Button
Application.ScreenUpdating = True
End Sub
Sub Add_Button()
Application.ScreenUpdating = False
Dim row As Long
row = ActiveSheet.Buttons("Add_Section").TopLeftCell.row
i = "=" & Cells(row - 11, 3).Value & ""
Set t = ActiveSheet.Range(Cells(row - 2, 2), Cells(row - 2, 2))
ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height).Select
Selection.Name = "Add_Wall" & i
Selection.OnAction = "Add_Wall2"
ActiveSheet.Shapes("Add_Wall" & i).Select
With Selection
.Characters.Text = "Add Wall"
With .Font
.Name = "Lucinda Grande"
.FontStyle = "Regular"
.Size = 12
End With
End With
Call Unhide
Application.ScreenUpdating = True
End Sub
Sub Unhide()
Application.ScreenUpdating = False
Dim row As Long
row = ActiveSheet.Buttons("Add_Section").TopLeftCell.row
Rows(row - 12 & ":" & row - 1).Select
Selection.EntireRow.Hidden = False
Range("BB100").Select
Call ProtectAll
Application.ScreenUpdating = True
End SubI have tried to replace the 'ActiveSheet' with 'Sheet 1' and I get this new error, "Run-time error '1004': Method 'Range' of object'_Worksheet' failed". Now it goes all the way through to my Add Button sub, and gets stuck here:
Code:
Sub Add_Button()
Application.ScreenUpdating = False
Dim row As Long
row = ActiveSheet.Buttons("Add_Section").TopLeftCell.row
i = "=" & Cells(row - 11, 3).Value & ""
Set t = ActiveSheet.Range(Cells(row - 2, 2), Cells(row - 2, 2))
ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height).Select
Selection.Name = "Add_Wall" & i
Selection.OnAction = "Add_Wall2"
ActiveSheet.Shapes("Add_Wall" & i).Select
With Selection
.Characters.Text = "Add Wall"
With .Font
.Name = "Lucinda Grande"
.FontStyle = "Regular"
.Size = 12
End With
End With
Call Unhide
Application.ScreenUpdating = True
End SubI have tried deleting all other worksheets except for Sheet 1 and the start 'Enable Macros' sheet. But I get the same errors. The only difference being that it doesn't take me to Sheet 3 upon closing the debugger.
I have even tried replacing the button, by creating a new button in Excel 2013. But nothing seems to solve the problem.
Now if you click on the newly created, by the above code, Add Wall button, on the first click it DOES WORK and adds a new row to the table. But on the second click it throws up this error, "Run-time error '-2147024809 (80070057)': The item with the specified name wasn't found". The forth time it works, and the fifth it doesn't, and so on... Here is the code and the point at which it gets stuck is underlined:
Code:
Sub Add_Wall2()
'
' Macro to add wall
'
Application.ScreenUpdating = False
Call UnprotectAll
Dim row As Long
row = ActiveSheet.Shapes(Application.Caller).TopLeftCell.row
Rows(row - 4).Insert
Cells(row - 5, 2).AutoFill Destination:=Range(Cells(row - 5, 2), Cells(row - 3, 2)), Type:=xlFillDefault
Call RightValues
Application.ScreenUpdating = True
End Sub
Sub ProtectAll()
Dim sh As Worksheet
Dim yourPassword As String
yourPassword = "xxx"
For Each sh In ActiveWorkbook.Worksheets
sh.Protect Password:=yourPassword
Next sh
End Sub
Sub UnprotectBook()
ActiveWorkbook.Unprotect Password:="xxx"
End Sub
Sub RightValues()
Application.ScreenUpdating = False
row = ActiveSheet.Shapes(Application.Caller).TopLeftCell.row
Cells(row - 5, 3).Value = Cells(row - 4, 3).Value
Cells(row - 4, 3).ClearContents
Call Unloc
Application.ScreenUpdating = True
End Sub
Sub Unloc()
row = ActiveSheet.Shapes(Application.Caller).TopLeftCell.row
ActiveSheet.Cells(row - 5, 3).Locked = False
Call ProtectAll
End SubI have tried adding in error handling, and this just stops the run-time error pop up from showing up, but the buttons only ever work every second click, which still is not good enough.
Please forgive me if I haven't written my code in the correct format, I am just not sure how to???
Please can someone help me solve this ASAP!
Thank you!
Last edited by a moderator:
