reference to another sheet not in wkbk brings up dialog box

[Valerie]

I have a macro that writes a formula referencing another sheet in the
workbook. When this sheet isn't in the workbook, Excel opens the dialog box
to find the sheet and repeats for every line that contains the reference. I
would like to add something around the section referring to another sheet to
bypass that section if the sheet is not in the workbook. Is this possible?

 

[Bernie Deitrick]

Valeire

Dim ShtName As String
'Other Code

On Error Goto NoSheet:
ShtName = Worksheets("Sheet that may not be there").Name
'Code for formula referencing
NoSheet:

'Finish up code

HTH,
Bernie
MS Excel MVP

 

[Valerie]

Perfect!! Thanks so much!

Valeire

Dim ShtName As String
'Other Code

On Error Goto NoSheet:
ShtName = Worksheets("Sheet that may not be there").Name
'Code for formula referencing
NoSheet:

'Finish up code

HTH,
Bernie
MS Excel MVP