Macro runs in debug mode perfectly fine, but when i try to run it normally it errors out

Xalova

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hello all.

The title basicly says it all. I have a macro which just adds sheets with a desired name. When i wrote the code i looked if it works as intended in debug mode. But as soon as i wanted to run the macro normally it errors out.

the code that i run is as follows:

VBA Code:
Sub killast()

With Worksheets("vorlage").Range("anzahltage2")
Dim kill As Integer
If .Value > 2 Then
sheetname = Sheets(Sheets("Vorlage").Index + 1).Name
kill = MsgBox("Willst du wirklich Das kalkulationsblatt ""K(" & .Value - 1 & ")""löschen?", vbQuestion + vbYesNo + vbDefaultButton1)
If kill = vbYes And .Value > 1 Then
sn = "K(" & .Value - 1 & ")"
Application.DisplayAlerts = False
Worksheets(sn).Unprotect
Sheets(sn).Delete
'Sheets("K(" & .Value - 2 & ")").Activate
Application.DisplayAlerts = True
Worksheets("vorlage").Unprotect
.Value = .Value - 1
Worksheets("vorlage").Protect

Else
Exit Sub
End If
Else
sn = Sheets(Sheets("Vorlage").Index + 1).Name
kill2 = MsgBox("Willst du wirklich die daten aus dem blatt """ & sn & """löschen?", vbQuestion + vbYesNo + vbDefaultButton1)
    If kill2 = vbYes Then
        Application.DisplayAlerts = False
         Sheets(sn).Unprotect
         Sheets(sn).Delete
        Application.DisplayAlerts = True
           Call Copyrenameworksheetss
    End If
End If
End With
End Sub

the code which gets called:

VBA Code:
Sub Copyrenameworksheetss()
'Updateby Extendoffice
    Dim ws As Worksheet
    Set wh = Worksheets(ActiveSheet.Name)
    With Worksheets("Vorlage").Range("AnzahlTage2")
    
    ThisWorkbook.Worksheets("Vorlage").Visible = True
        Sheets("Vorlage").Copy after:=Sheets(Sheets.Count)
    
    If IsNumeric(.Value) Then
    'ActiveSheet.Name = "Tag " & .Value
    If Not Sheets.Count - Sheets("Vorlage").Index = .Value Then
    Sheets("Vorlage").Unprotect
    .Value = Sheets.Count - Sheets("Vorlage").Index - 1
    Sheets("Vorlage").Protect
    End If
    If Sheets.Count - Sheets("Vorlage").Index = .Value + 1 Then
    Sheets("Vorlage").Unprotect
    .Value = Sheets.Count - Sheets("Vorlage").Index
    sn = "K(" & .Value & ")"
    Sheets("Vorlage").Protect
    End If
    sn = "K(" & .Value & ")"
    ActiveSheet.Name = "K(" & .Value & ")"
    'Sheets("Tag " & .Value).Tab.ColorIndex = 10
    Sheets(sn).Tab.ColorIndex = 10
    Worksheets("Vorlage").Unprotect
    .Value = .Value + 1
    Worksheets("Vorlage").Protect
    ThisWorkbook.Worksheets("Vorlage").Visible = False
    End If
    GoTo ciao
    End With
ciao:
End Sub

it errors out on
VBA Code:
 Worksheets("Vorlage").Unprotect
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
Are you sure it is erroring out on:-
VBA Code:
Worksheets("Vorlage").Unprotect

I can occasionally get it to fail on:-
VBA Code:
Worksheets(sn).Unprotect
And that is because it is looking for a particular K(n) say K(2) and it is not there.

Note: the only way Unprotect seems to be able to fail is for the sheet it is unprotecting to not exist.

I am bit concerned that by copying the sheet Vorlage you are also creating multiple copies of the range name "anzahltage2" which seems undesirable.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
Oops apparently my K with an "n" was the code for a thumbs down emoji and I didn't notice.
 

Xalova

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Oops apparently my K with an "n" was the code for a thumbs down emoji and I didn't notice.
yes i am absolutly sure. I just checked.

also if i run the macro in the vba manager(idk how its called tbh) it works too. Only if i try to run the code via a button, it errors out
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

And the code is definitely in the same workbook as the worksheets and is in a standard module not in a sheet or the workbook code module.
And does it work when you run it from alt+f8 ?
Perhaps add a line before that code:
debug.print activeworkbook.name
 

Xalova

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
And the code is definitely in the same workbook as the worksheets and is in a standard module not in a sheet or the workbook code module.
And does it work when you run it from alt+f8 ?
Perhaps add a line before that code:
debug.print activeworkbook.name
1st, yes. I am in the same workbook. I know thar because its the only workbook open.

2nd, i dont know what should happen, but when i press alt+f8 nothing happens at all.

3rd, it does not change anything if put
VBA Code:
debug.print activeworkbook.name
before the errorcode
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,301
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Do you have a personal workbook and if yes is the code in it?
3rd, it does not change anything if put
VBA Code:
debug.print activeworkbook.name
before the errorcode
You need to look in the Immediate window, if you can't see the Immediate window while in the VBE press Ctrl + G (you might need to expand/resize it)
 

Forum statistics

Threads
1,147,498
Messages
5,741,503
Members
423,663
Latest member
kaveh87rsh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top