Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking to see a sheet of the same name exsists
Hi, I orginally started this code by recording it from a macro and then have put in some vba surrounding it so its a bit messy! The code is behind a button on a user form. What it does is create a copy of a sheet called "Master" and then names it the date which has been entered on to a text box - tbDate -on a user form. What I would like to do is check to see if the name that has been entered in to tbDate is already a sheet name and if so stop the procedure and pop up with a text box, but if it doesn't exsist then keep on doing the rest of the code. I've tried doing it with some code I found on http://www.ozgrid.com/VBA/IsWorkbookOpen.htm which states: Code: -------------------- Sub DoesSheetExist() ''''''''''''''''''''''''''''''''''''' 'Written by www.OzGrid.com 'Test to see if a Worksheet exists. ''''''''''''''''''''''''''''''''''''' Dim wSheet As Worksheet On Error Resume Next Set wSheet = Sheets("Sheet1") If wSheet Is Nothing Then 'Doesn't exist MsgBox "Worksheet does not exist", _ vbCritical,"OzGrid.com" Set wSheet = Nothing On Error GoTo 0 Else 'Does exist MsgBox "Sheet 1 does exist", _ vbInformation,"OzGrid.com" Set wSheet = Nothing On Error GoTo 0 End If End Sub -------------------- But I couldn't get this working with my code, or with using information from tbDate The code which I am working with is: Code: -------------------- Private Sub cmdNewSheet_Click() On Error GoTo Err_Command1_Click Dim date1 As Long Application.ScreenUpdating = False Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = "1" Sheets("1").Select Sheets("Master").Select Cells.Select Selection.Copy Sheets("1").Select Range("A1").Select ActiveSheet.Paste Range("A1").Select Sheets("Master").Select Range("A1").Select Application.CutCopyMode = False Sheets("1").Select Range("b1").Select Selection.ClearContents ActiveCell.Value = Me.tbDate.Value Application.CutCopyMode = False 'Puts the date in the correct format and then enters it as the sheets name tbDate.Value = Format(tbDate.Value, "dd-mm-yy") Sheets("1").Name = Me.tbDate.Value Range("A1").Select Unload frmEnterDate ActiveWindow.zoom = 70 Application.ScreenUpdating = True Exit_Command1_Click: Exit Sub Err_Command1_Click: MsgBox ("The sheet you created already exsists!") 'Delete a sheet Application.DisplayAlerts = False Sheets("1").Delete 'ActiveSheet.Delete 'ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True Resume Exit_Command1_Click End Sub -------------------- Any help would be very much appericated, I hope all this makes sense! Thanks a lot, Dave :) :) :) -- beans_21 ------------------------------------------------------------------------ beans_21's Profile: http://www.excelforum.com/member.php...o&userid=30281 View this thread: http://www.excelforum.com/showthread...hreadid=501031 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking to see a sheet of the same name exsists
Hi Dave,
Add this code to the beginning of Private Sub cmdNewSheet_Click() -------------- Dim sht As Worksheet Dim str As String str = Me.tbDate.Value For Each sht In ThisWorkbook.Worksheets If sht.Name = str Then MsgBox "A sheet with this name already exists", vbOKOnly + vbExclamation, str Exit Sub End If Next ------------------ This will loop thru all the existing sheets and checkes there names. Hoop This Helps, Executor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking to see a sheet of the same name exsists
One way:
Private Sub cmdNewSheet_Click() Dim sTemp As String Dim wsTest As Worksheet With tbDate If IsDate(.Text) Then sTemp = Format(.Text, "dd-mm-yy") On Error Resume Next Set wsTest = Worksheets(sTemp) On Error GoTo 0 If Not wsTest Is Nothing Then MsgBox "The sheet you created already exists!" Else Worksheets("Master").Copy After:=Sheets(Sheets.Count) With Sheets(Sheets.Count) .Name = sTemp .Range("B1").Value = sTemp End With End If ActiveWindow.Zoom = 70 End If End With Application.ScreenUpdating = True Unload Me End Sub In article , beans_21 wrote: I orginally started this code by recording it from a macro and then have put in some vba surrounding it so its a bit messy! The code is behind a button on a user form. What it does is create a copy of a sheet called "Master" and then names it the date which has been entered on to a text box - tbDate -on a user form. What I would like to do is check to see if the name that has been entered in to tbDate is already a sheet name and if so stop the procedure and pop up with a text box, but if it doesn't exsist then keep on doing the rest of the code. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking to see a sheet of the same name exsists
Thank you both for your responses! I decied to use JE McGimpsey, it worked perfectly, thank you so much for your help :) :) :) Dave -- beans_21 ------------------------------------------------------------------------ beans_21's Profile: http://www.excelforum.com/member.php...o&userid=30281 View this thread: http://www.excelforum.com/showthread...hreadid=501031 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"the file already exsists in this location do you want to replace. | Excel Discussion (Misc queries) | |||
If value already exsists in range.... | Excel Programming | |||
Checking if sheet is visible | Excel Discussion (Misc queries) | |||
Checking to see if a sheet is open | Excel Programming | |||
Check if Userform exsists before deleting via VB | Excel Programming |