Avoid VBA errors with the same page name

knaabis

Board Regular
Joined
Apr 25, 2006
Messages
246
Office Version
  1. 2013
Platform
  1. Windows
How to integreate in this VBA - when I try to create a page by the existing name, then that VBA is suspended?

Sub SheetNumber()
DialogSheets("Dialog1").Show
Sheets.Add.Name = DialogSheets("Dialog1").EditBoxes("4").Text
With DialogSheets("Dialog1")
.EditBoxes("4").Text = ""
End With
End Sub
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
If it turns out the sheet already exists, do you need to name the new one something else (if so, what's the naming convention?) or does the sheet preexisting mean you don't need to create it?

Here's a function for identifying whether a named sheet exists in the current workbook, returning TRUE if it does.

Code:
Function sheetExists(ByVal shtName As String) As Boolean
    On Error Resume Next
    sheetExists = ActiveWorkbook.Sheets(shtName).Name <> ""
End Function
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,232
Office Version
  1. 365
Platform
  1. Windows
Why are you using DialogSheets?

As far as I'm aware they were last used in the early 90s and were 'replaced' by such things as userforms.
 

knaabis

Board Regular
Joined
Apr 25, 2006
Messages
246
Office Version
  1. 2013
Platform
  1. Windows
It's something to be changed, if I don't use user form?
:)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,232
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sorry, I don't quite know what you mean - but then again I don't actually know what you are trying to do or what the problem is.

You seem to be trying to create a new (dialog) sheet of some sort with the name taken from an existing (dialog) sheet.

Is that anywhere near what you are trying to do?:)
 

knaabis

Board Regular
Joined
Apr 25, 2006
Messages
246
Office Version
  1. 2013
Platform
  1. Windows
I have many sheets in Workbook.
I need to create a new sheet, which has a name as DialogSheets("Dialog1").EditBoxes("4").Text

When i create the new sheet and sheet with this new name exist , then i get error in VBA...
There is also a problem ...
 

knaabis

Board Regular
Joined
Apr 25, 2006
Messages
246
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Ideas?
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
I thought I had.

What do you want to do if a sheet with the same name as the text in

DialogSheets("Dialog1").EditBoxes("4").Text

already exists?
 

knaabis

Board Regular
Joined
Apr 25, 2006
Messages
246
Office Version
  1. 2013
Platform
  1. Windows
I need to stop the current VBA and get message - "The sheet exist"
 

Watch MrExcel Video

Forum statistics

Threads
1,123,399
Messages
5,601,459
Members
414,451
Latest member
jrose7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top