PDA

View Full Version : [SOLVED] xlveryhidden



stapuff
01-14-2005, 07:36 AM
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
01-14-2005, 07:40 AM
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
01-14-2005, 07:57 AM
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
01-14-2005, 08:31 AM
I just tried your code as is and it worked fine. Can you post an attachment that is getting the error?

stapuff
01-14-2005, 08:49 AM
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
01-14-2005, 09:18 AM
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
01-14-2005, 09:37 AM
Thanks DRJ for all your suggestions.



Take Care,



Kurt

Jacob Hilderbrand
01-14-2005, 06:26 PM
You're Welcome

Take Care