IF statement for future worksheet???

Big Lar

Well-known Member
Joined
May 19, 2002
Messages
552
During UserForm Initialize…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
How to ignore
<o:p></o:p>
Code:
Label107.Caption = Sheet8.Range("A3").Value
<o:p></o:p>
If Sheet8 does not yet exist?
<o:p></o:p>
I’ve tried several If statements without success.
<o:p></o:p>
Sheet8 is created via Module1 code…and will always be named “<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:place w:st="on">PO</st1:place> (2)”

Thanks for looking.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,786
Office Version
  1. 2010
Platform
  1. Windows
Assuming the worksheet has been created when the code runs,

Code:
    Label107.Caption = Worksheets("PO(2)").Range("A3").Value
 

Big Lar

Well-known Member
Joined
May 19, 2002
Messages
552
Ah...there's the rub.

UserForm1 CommandButton1 runs Module1.
Module1 Hides UserForm1
Module1 does stuff including creating sheet "PO (2)" and...
UserForm1.Show.

Private Sub UserForm_Initialize()
would then display:

Label107.Caption = Sheet8.Range("A3").Value
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,786
Office Version
  1. 2010
Platform
  1. Windows
I'm not sure what rub you're referring to, but you can't use the codename of a sheet that doesn't exist; the code won't compile.

You can use the name of a non-existent sheet, as long as the sheet is created before it runs.
 

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638

ADVERTISEMENT

To check if a sheet exists before referencing it, you could use something like:
Code:
Dim ws As Worksheet

On Error Resume Next
Set ws = Sheets("PO (2)")
If Len(ws.Name) = 0 Then  'Sheet does not exist
    On Error GoTo 0
    MsgBox "Sheet ""PO (2)"" does not exist"
    Exit Sub
End If ' or do something else, run more code, etc.
 

Big Lar

Well-known Member
Joined
May 19, 2002
Messages
552
The rub?

Assuming the worksheet has been created when the code runs...

The worksheet is created by the code. I'm trying to display results on the UserForm after "PO (2)" is created and data sorted.

I was hoping an IF statement in UserForm1_Initialize would do the trick. I'll have to rethink the process.

Thanks for the assistance.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,786
Office Version
  1. 2010
Platform
  1. Windows
As long as the worksheet has been created when that line (my line) of code executes, you're fine.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,138
Messages
5,640,337
Members
417,139
Latest member
madcabbie

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