Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding worksheet
Hi all,
I have a workbook that adds worksheets, names them and sorts them. The naming is done by a cell entry from a userform. I would like to check for a worksheet with the same name as the entry from the userform then ask the user if they want to add another worksheet with that name or activate the last worksheet made with that name. As in another post I have, the worksheets could be DBL ARROW for worksheet1 name, if the user enters DBL ARROW again then it would ask them if they want another worksheet with the same name, if they do it would create DBL ARROW (2), if the user enters DBL ARROW again then it would ask them if they want another worksheet with the same name, if they do it would create DBL ARROW (3), if they do not want another with the same name then they could activate the last one made, which was DBL ARROW (2), or they could cancel and not create another worksheet with that name. Is there a way to do this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding worksheet
"jnf40" wrote in message ... Hi all, I have a workbook that adds worksheets, names them and sorts them. The naming is done by a cell entry from a userform. I would like to check for a worksheet with the same name as the entry from the userform then ask the user if they want to add another worksheet with that name or activate the last worksheet made with that name. As in another post I have, the worksheets could be DBL ARROW for worksheet1 name, if the user enters DBL ARROW again then it would ask them if they want another worksheet with the same name, if they do it would create DBL ARROW (2), if the user enters DBL ARROW again then it would ask them if they want another worksheet with the same name, if they do it would create DBL ARROW (3), if they do not want another with the same name then they could activate the last one made, which was DBL ARROW (2), or they could cancel and not create another worksheet with that name. Is there a way to do this? For i=1 to worksheets.count if sheets(i).name=<NEW NAME then if msgbox("A sheet with this name exists, do you want to use that sheet"),vbYesNo)=vbNo then 'Add new sheet exit for else sheets(i).activate exit for endif endif next if i worksheets.count then 'Add sheet endif Pete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding worksheet
I didn't use a userform, but this worked with a simple InputBox:
Option Explicit Sub testme01() Dim TestSht As Object Dim UserShtName As String Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet UserShtName = InputBox("Enter name") OkToAdd = False If SheetExists(UserShtName) = False Then 'doesn't exist OkToAdd = True Else 'match upper/lower case of existing sheet name UserShtName = Sheets(UserShtName).Name resp = MsgBox("That name already exists." & _ vbLf & "Want to make another?", Buttons:=vbYesNo) If resp = vbNo Then 'don't change oktoadd Else OkToAdd = True End If End If If OkToAdd = True Then Set wks = Worksheets.Add Call GiveItANiceName(UserShtName, wks) End If End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0) End Function Sub GiveItANiceName(myPFX As String, wks As Worksheet) Dim iCtr As Long Dim mySFX As String Dim myStr As String Do If iCtr = 0 Then myStr = "" Else myStr = " (" & iCtr & ")" End If On Error Resume Next wks.Name = myPFX & mySFX & myStr If Err.Number < 0 Then Err.Clear Else Exit Do End If On Error GoTo 0 iCtr = iCtr + 1 Loop End Sub jnf40 wrote: Hi all, I have a workbook that adds worksheets, names them and sorts them. The naming is done by a cell entry from a userform. I would like to check for a worksheet with the same name as the entry from the userform then ask the user if they want to add another worksheet with that name or activate the last worksheet made with that name. As in another post I have, the worksheets could be DBL ARROW for worksheet1 name, if the user enters DBL ARROW again then it would ask them if they want another worksheet with the same name, if they do it would create DBL ARROW (2), if the user enters DBL ARROW again then it would ask them if they want another worksheet with the same name, if they do it would create DBL ARROW (3), if they do not want another with the same name then they could activate the last one made, which was DBL ARROW (2), or they could cancel and not create another worksheet with that name. Is there a way to do this? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding worksheet
I copied that last subroutine from a previous post. mySfx isn't required in
your case. Toss that earlier version and replace it with: Sub GiveItANiceName(myPFX As String, wks As Worksheet) Dim iCtr As Long Dim myStr As String Do If iCtr = 0 Then myStr = "" Else myStr = " (" & iCtr & ")" End If On Error Resume Next wks.Name = myPFX & myStr If Err.Number < 0 Then Err.Clear Else Exit Do End If On Error GoTo 0 iCtr = iCtr + 1 Loop End Sub Dave Peterson wrote: I didn't use a userform, but this worked with a simple InputBox: Option Explicit Sub testme01() Dim TestSht As Object Dim UserShtName As String Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet UserShtName = InputBox("Enter name") OkToAdd = False If SheetExists(UserShtName) = False Then 'doesn't exist OkToAdd = True Else 'match upper/lower case of existing sheet name UserShtName = Sheets(UserShtName).Name resp = MsgBox("That name already exists." & _ vbLf & "Want to make another?", Buttons:=vbYesNo) If resp = vbNo Then 'don't change oktoadd Else OkToAdd = True End If End If If OkToAdd = True Then Set wks = Worksheets.Add Call GiveItANiceName(UserShtName, wks) End If End Sub Function SheetExists(SheetName As Variant, _ Optional WhichBook As Workbook) As Boolean 'from Chip Pearson Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0) End Function Sub GiveItANiceName(myPFX As String, wks As Worksheet) Dim iCtr As Long Dim mySFX As String Dim myStr As String Do If iCtr = 0 Then myStr = "" Else myStr = " (" & iCtr & ")" End If On Error Resume Next wks.Name = myPFX & mySFX & myStr If Err.Number < 0 Then Err.Clear Else Exit Do End If On Error GoTo 0 iCtr = iCtr + 1 Loop End Sub jnf40 wrote: Hi all, I have a workbook that adds worksheets, names them and sorts them. The naming is done by a cell entry from a userform. I would like to check for a worksheet with the same name as the entry from the userform then ask the user if they want to add another worksheet with that name or activate the last worksheet made with that name. As in another post I have, the worksheets could be DBL ARROW for worksheet1 name, if the user enters DBL ARROW again then it would ask them if they want another worksheet with the same name, if they do it would create DBL ARROW (2), if the user enters DBL ARROW again then it would ask them if they want another worksheet with the same name, if they do it would create DBL ARROW (3), if they do not want another with the same name then they could activate the last one made, which was DBL ARROW (2), or they could cancel and not create another worksheet with that name. Is there a way to do this? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Data in a worksheet | Excel Discussion (Misc queries) | |||
Finding Worksheet and Activating it | Excel Discussion (Misc queries) | |||
finding the last row in another worksheet | Excel Worksheet Functions | |||
Finding the current worksheet name | Excel Programming | |||
Finding Worksheet Name | Excel Programming |