11

I'm having a problem when adding new sheets to a Workbook from a UserForm. Basically whenever I add a new sheet, it appears to be linked to another sheet in the same workbook.

I have been able to reproduce this issue fairly easily by creating a new Excel file, then adding it a new userform with this code in it: 

Private Sub UserForm_Click()
    Sheets.Add
    Call Unload(Me)
End Sub

If I open the userform from a sheet such as this one:

enter image description here

And then I double click the userform, the issue already happens.

The new sheet is works incorrectly. For example if I write on any cell of the new sheet I get the formatting from the original sheet. Worse still, if the original sheet is protected, I'm not allowed to write on the new sheet (though the ribbon shows it's unprotected).

enter image description here

If I select another sheet and select the new sheet again everything returns to normal. Am I doing something wrong? If it's a bug is there any workaround?

Here is an Excel file that produces the error: Drive.google.com

0m3r
  • 12,286
  • 15
  • 35
  • 71
lisovaccaro
  • 32,502
  • 98
  • 258
  • 410
  • 4
    Your issue from your example file doesn't appear in Excel 2010. – PatricK Mar 03 '16 at 22:46
  • It's happening in 2013 Office Excel with Form Control Command Button and Shapes. Using ActiveX control command button on sheet is not causing this issue. – Sixthsense Mar 04 '16 at 07:14
  • 1
    I can replicate the initial formatting issue with xl2016 - but as soon as the text entry is made the yellow background that is there in edit mode disappears. Howevere, other formats (bold text etc) are retained from the first sheet. Interesting. – brettdj Mar 04 '16 at 07:25
  • @brettdj I've seen different behaviors caused by the issue. Sometimes when you write in the new sheet the original one changes, other times you can't move between cells with the arrow keys. – lisovaccaro Mar 04 '16 at 11:42
  • I've seen it in 2013. It looks like there is a problem with calling `sht.Activate` method from modal form. It makes the `sht` visible but not active. The only workaround I remember was replacing form button with ActiveX button. – BrakNicku Mar 04 '16 at 19:19
  • Just a Hunch (don't have office 2013) you can bypass this issue by creating a Sub in normal Module to Add Worksheet - say `Sub MyAddSheet()`, then in the `UserForm_Click()`, add `Application.OnTime(Now + TimeSerial(0,0,1),"MyAddSheet")` before `Unload Me`. You may need to increase the seconds if it still happens. – PatricK Mar 06 '16 at 22:36

4 Answers4

3

In Userform1's properties, change the ShowModal to False.

I was able to re-produce the behavior. I changed the ShowModal to False and the behavior changed to the expected behavior. I made no changes to any VBA code.

I'm running 64-bit Excel 2013 from MS Office Professional.

B540Glenn
  • 409
  • 2
  • 6
0

I think, it somehow connected with ShowModal property, may be some bug.

Workaround

Add UserForm2, set it's ShowModal property to false, edit the code.

Code for UserForm1:

Private Sub UserForm_Click()

    Unload Me

End Sub

Private Sub UserForm_Terminate()
    UserForm2.Show
End Sub

Code for UserForm2:

Private Sub UserForm_Activate()
    Sheets.Add
    Unload Me
    End
End Sub

The idea is: close old UserForm, make new one which will add a sheet.

Edit #1

Feel free to download working example and try it out.

More simple way?

I also noticed that when userForm1 terminates, code in Module1 continues to run. So why not just add new sheet there? Code for module1 would be:

Sub ShowForm()
    UserForm1.Show
    Sheets.Add
End Sub

And code for UserForm1:

Private Sub UserForm_Click()
    Unload Me
End Sub
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • @lisovaccaro, I've added more details to my answer. Please let me know if it doesn't work for you and why. Thank you. – Max Makhrov Mar 06 '16 at 10:54
0

This is a good one, never seen anything like it before.

I was able to replicate your error and come up with a workaround, but it may not be exactly what you're looking for.

I noticed in testing that if you run the macro, select the original sheet, then re-select the newly created sheet, the formatting goes back to normal (as you stated in your question). So I added in Sheets(2).Select to the end of your Private Sub. Then when I selected the new sheet it was normal formatting.

I also tried Sheets(2).Select followed by Sheets(1).Select, but it didn't work (Sheets(1) being the newly created sheet).

Very bizarre issue! Hopefully this will work for you (might need to adjust the Sheets(2) to match the name or location of your original sheet), and that ending on the original sheet is OK.

Private Sub UserForm_Click()
    Sheets.Add
    Call Unload(Me)
    Sheets(2).Select
End Sub

The above works under the assumption you are in a workbook with only 1 sheet, which is where the "Click Me" button is.

TheGuyThatDoesn'tKnowMuch
  • 1,240
  • 1
  • 17
  • 28
0

Although this isn't affecting Excel 2010, please try this different approach.

Note: You may need to increase time if it still happens.

In your UserForm, add:

Option Explicit

Private Sub UserForm_Click()
    AddWorksheetTask 1 ' invokes task to Delay 1 second
    Unload Me
End Sub

Then in a normal module, add:

Option Explicit

Sub AddWorksheetTask(Seconds As Integer)
    Application.OnTime Now + TimeSerial(0, 0, Seconds), "AddWorkSheet"
End Sub

Sub AddWorksheet()
    If UserForm1.Visible Then ' <-- Change to your Form name
        AddWorksheetTask 1 ' Reschedule task for next second
    Else
        Sheets.Add
    End If
End Sub
PatricK
  • 6,375
  • 1
  • 21
  • 25