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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding worksheet
If you have Arrow, Arrow (1), Arrow (2), Arrow (3), Arrow (9), this will find
that missing "arrow (4)" and stop there. It works ok if there are no gaps in names. Option Explicit Sub testme01() Dim TestSht As Object Dim UserShtName As String Dim OkToAdd As Boolean Dim resp As Long Dim wks As Worksheet Dim LargestSheet 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 Set LargestSheet = FindLargestSheet(UserShtName) If LargestSheet Is Nothing Then MsgBox "Error--this shouldn't happen" Else LargestSheet.Activate End If 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 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 Function FindLargestSheet(myPFX As String) As Worksheet Dim iCtr As Long Dim SheetToUse As Worksheet Dim myStr As String Set SheetToUse = Nothing iCtr = 0 Do If iCtr = 0 Then myStr = "" Else myStr = " (" & iCtr & ")" End If If SheetExists(myPFX & myStr) Then Set SheetToUse = Sheets(myPFX & myStr) 'and keep looking Else 'get out Exit Do End If iCtr = iCtr + 1 Loop Set FindLargestSheet = SheetToUse End Function ============== If you have gaps in names, then maybe you could estimate the largest (#) used and just count backwards until one is found. Then use that. jnf40 wrote: Dave another question about this...if the user chooses no what would be the code to pick the worksheet with the largest iCtr...say they had sheets named ARROW...ARROW (2)... and ARROW (3)...and they chose No it would automatically activate the ARROW (3) sheet. "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 |