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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,785
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,785
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,785
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,054
Messages
5,639,796
Members
417,114
Latest member
JPFaulkner

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