xlveryhidden

[stapuff]

When a sheet is xlveryhidden is it no longer an activesheet?

I have code that when a user inserts a new sheet a userform appears, making the new sheet xlveryhidden until the userform is completed. On cmdbutton click show the sheet and name it. However, it does not recognize the new sheet so it renames the last active sheet.

Thanks,

Kurt

[Jacob Hilderbrand]

When the sheet is hidden another sheet will be activated. You can refer to the sheet directly though like this:

Dim WS              As Worksheet
Set WS = ActiveSheet
     WS.Visible = xlSheetVeryHidden
'Code here
WS.Name = "New Name"
'More code here

[stapuff]

Jacob - thanks for your reply.


I am going to post the code I have. Please take a look and see what needs to be done.

In ThisWorkBook I have:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 UserForm4.Show
 End Sub

In Userform4 I have:

Private Sub CommandButton1_Click()
 With ActiveSheet
 Dim shname As String
 shname = StrConv(Me.TextBox1.Text, vbProperCase)
 Dim i As Long, hasBadVal As Boolean
 hasBadVal = False
 For i = 1 To Len(Me.TextBox1.Value) Step 1
 Select Case Mid(Me.TextBox1.Value, i, 1)
 Case "?", "/", "\", ":", "]", "[", "*"
 hasBadVal = True
 Exit For
 End Select
 Next i
 If hasBadVal Then
 MsgBox "An unexceptable name has been entered into the textbox!", _
 vbCritical, "ERROR"
 Me.TextBox1.Value = ""
 Me.TextBox1.SetFocus
 Else
 ActiveSheet.Name = shname
 UserForm4.Hide
 Unload Me
 End If
 End With
 End Sub

I tried adding your code to the Thisworkbook section. I am getting the same results.

Thanks,

Kurt

[Jacob Hilderbrand]

I just tried your code as is and it worked fine. Can you post an attachment that is getting the error?

[stapuff]

Jacob -

You are correct. The code I posted does work but it allows the sheet to be visible. What I was trying to do is not allow the user to see the sheet being added until after the userform was completed.

I couldn't get this to work so I prevented the user from canceling out of the userform by adding the following code:

Private Sub UserForm_QueryClose _
    (Cancel As Integer, CloseMode As Integer)
  '   Prevents use of the Close button
      If CloseMode = vbFormControlMenu Then
          MsgBox "Close button has been disabled."
          Cancel = True
      End If
  End Sub

plus I added a cancel button on the userform with the following code to force sheet deletion is the user decided the sheet was not needed.

Private Sub CommandButton2_Click()
  ActiveSheet.Select
  Application.SendKeys ("{ENTER}")
  Application.Wait Now + TimeSerial(0, 0, 1)
  ActiveWindow.SelectedSheets.Delete
  UserForm4.Hide
  Unload Me
  End Sub

Now,Now - do not scold me for using sendkeys. I know you do not like the use of them. I added this one to accept the "do you want the delete the sheet" message box

Thanks,

Kurt

[Jacob Hilderbrand]

Definitely do not use Send Keys unless it is a last resort. If you want to stop the message from displaying try this:

Application.DisplayAlerts = False

How about trying something like this:

Option Explicit
 
 Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.DisplayAlerts = False
     Sh.Delete
     Application.DisplayAlerts = True
     UserForm4.Show
End Sub

Then in your UserForm code do whatever you want and when you are ready to add a new sheet try this:

Application.EnableEvents = False
     Sheets.Add.Name =shname
     Application.EnableEvents = True

Also if you use vba tags instead of code tags the code in your post will look like it would in the VBE.

[stapuff]

Thanks DRJ for all your suggestions.



Take Care,



Kurt

[Jacob Hilderbrand]

You're Welcome

Take Care