Userform dissapearing / resetting when VBA deletes a sheet

[LeeRowe]

Good morning all. I have an issue that is driving me crazy and have spent way too long trying to search / debug a solution. Please bear with me as this is my first post here.

Essentially, I have a userform that I activate within my spreadsheet for a few parameters for filtering of data. Once the OK is selected, I delete a couple of sheets and re-create them from scratch. This all works just fine for the most part, except that deleting one particular sheet causes the userform to fully reset back to scratch. I normally hide the userform prior to running the function that calls the delete, then when I display it again, everything is reset. If I dont hide it, then the delete function closes it down anyhow (I assume the userform is being fully unloaded from memory).

The same function deletes both sheets, yet only one sheet seems to cause the userform to reset. Whether its modal or not has made no difference.

Any help would be appreciated. I have posted the function that I call to delete / replace the sheet below. Even if I dont call this, and just slot an inline sheet.delete in place, I get the same result.

note:
1. The userform isnt necessarily called from the sheet being deleted
2. The sheet that causes problems does have VBA code attached to it, but even if I delete the code, it didnt help.

Sub DuplicateTemplate(TemplateName As String, NewSheetName As String)
' ********************** Unhide template sheet, duplicate it, rename it and re-hide the template ************************


Dim NewDataSheet As Worksheet
        
' *** Remove any duplicate sheet copy
Application.DisplayAlerts = False
On Error GoTo errCopyBlankTemplate
Sheets(NewSheetName).Delete
errCopyBlankTemplate:
On Error GoTo 0
Application.DisplayAlerts = True


' *** Unhide template and copy / rename
ThisWorkbook.Worksheets(TemplateName).Visible = True
ThisWorkbook.Worksheets(TemplateName).Copy _
after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Set DataSheet = ActiveSheet
DataSheet.Name = NewSheetName
ThisWorkbook.Worksheets(TemplateName).Visible = False


End Sub

 

[LeeRowe]

And as luck would have it, a couple of hours after finally asking the question, I worked out the answer.

The sheet that I was deleting contained ActiveX buttons. This caused any userforms to unload from memory as well as preventing any breaks into debug mode. Once I replaced the ActiveX buttons with regular Form Control buttons, all my problems magically went away.


I am just posting this one out there as this was a total nightmare for me to track down, and hopefully someone else may benefit from this one.

Cheers.

Sub DuplicateTemplate(TemplateName As String, NewSheetName As String)
' ********************** Unhide template sheet, duplicate it, rename it and re-hide the template ************************


Dim NewDataSheet As Worksheet
        
' *** Remove any duplicate sheet copy
Application.DisplayAlerts = False
On Error GoTo errCopyBlankTemplate
Sheets(NewSheetName).Delete
errCopyBlankTemplate:
On Error GoTo 0
Application.DisplayAlerts = True


' *** Unhide template and copy / rename
ThisWorkbook.Worksheets(TemplateName).Visible = True
ThisWorkbook.Worksheets(TemplateName).Copy _
after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Set DataSheet = ActiveSheet
DataSheet.Name = NewSheetName
ThisWorkbook.Worksheets(TemplateName).Visible = False


End Sub

[/QUOTE]

 

[nilmol]

Hi,

I'm suffering from the same issue, when a worksheet is deleted with code from clicking a button on a userform the userform itself also unloads unwantedly.
I have some ActiveX controls on the sheet, no buttons - only textboxes. But using Form controls instead has proven to come with other issues like changing fonts and positioning of the textboxes on the form when screen resolution is changed.

So, is there another way than changing to FormControls to prevent the UserForm from closing?

Cheers /Nils

 

[RoryA]

There isn't a Form control textbox, so it's not clear to me what you are referring to, or what that might have to do with userform controls changing.

 

[nilmol]

Well, actually there is, but it's not available it's greyed out which makes it impossible to use.

Anyway, what I'm saying is: formcontrols are not for me.
I'm in need for another solution to prevent my userform from closing when deleting a worksheet (that has ActiveX-controls on it).

/nils

 

[RoryA]

Activex controls are best avoided, for many reasons besides this. AFAIK, you can’t prevent this behaviour. You’d have to save and reload the form.

 

[nilmol]

I've tried that - with the .show command but it doesn't work. Nothing happens. Setting EnableEvents to False doesn't help either.
However, deleting the sheet manually/directly without using vba code leaves the user form still open and untouched. Very strange.
Must be some sort of flaw in Excel, I think.
/n

 

[Jaafar Tribak]

I couldn't reproduce the issue .

I added an activeX commandbutton to the activesheet then I run the userform (tried both Modal & Modeless) and deleted the activesheet programmatically by clicking on a button on the userform.
The sheet was deleted and the userform never unloads.

Could this be version -related .. I use excel 2016.

 

[nilmol]

Strange. I tried the opposite, added a sheet with no controls at all and then deleted it from the userform. Which worked perfectly fine without the userform unloading.
And I use the latest version of Excel 16.0 (12730).

Maybe I need better contact with "the force"?

/n