How to stop the 'select sheet' window pop up
 

Hi,

Is there a way i could stop the 'select sheet' window from appearing.

I am working with external refrences, the below code runs and populate
the cells but sometimes i have some sheets missing. When this happens
get a 'select sheet' window asking me to select a sheet and i have t
cancel it everytime.

Help !! :confused:

Here is the code... that i am using

-Sub Button43_Click()

On Error Resume Next
Dim str As Date
Dim comm As String
Dim strCom As String

For i = 33 To 39

strCom = "'!$B$16"
str = Worksheets("COCUBO").Cells(i, 1).Value
str1 = Format(str, "DD-MMM-YY")
Dim day1 As String, mon As String, year1 As String
day1 = Mid(str1, 1, InStr(1, str1, "-") - 1)
mon = UCase(Mid(str1, 4, InStr(3, str1, "-")))
year1 = Mid(str1, 8, InStr(7, str1, "-") + 1)
comm
"='\\ip.address\folder\subfolder\subfolder\[filename.xls]ABC-"
comm = comm & day1 & "-" & mon & "-" & year1 & strCom

'the above comm and strcom are for worksheet naming

Sheet2.Cells(i, 5).Formula = comm


'Sheet2.Cells.Characters(
If InStr(1, Sheet2.Cells(i, 5).Value, "Error") 0 Then

Sheet2.Cells(i, 5) = ""
End If

Next


End Sub

--
deepakmeht
-----------------------------------------------------------------------
deepakmehta's Profile: http://www.excelforum.com/member.php...fo&userid=2960
View this thread: http://www.excelforum.com/showthread.php?threadid=49317

How to stop the 'select sheet' window pop up
 

You may want to look at some of the code that Ron de Bruin has at:
http://www.rondebruin.nl/ado.htm

Me. I'd just open the workbook and see if the sheet were there, then close the
workbook.

deepakmehta wrote:

Hi,

Is there a way i could stop the 'select sheet' window from appearing.

I am working with external refrences, the below code runs and populates
the cells but sometimes i have some sheets missing. When this happens i
get a 'select sheet' window asking me to select a sheet and i have to
cancel it everytime.

Help !! :confused:

Here is the code... that i am using

-Sub Button43_Click()

On Error Resume Next
Dim str As Date
Dim comm As String
Dim strCom As String

For i = 33 To 39

strCom = "'!$B$16"
str = Worksheets("COCUBO").Cells(i, 1).Value
str1 = Format(str, "DD-MMM-YY")
Dim day1 As String, mon As String, year1 As String
day1 = Mid(str1, 1, InStr(1, str1, "-") - 1)
mon = UCase(Mid(str1, 4, InStr(3, str1, "-")))
year1 = Mid(str1, 8, InStr(7, str1, "-") + 1)
comm =
"='\\ip.address\folder\subfolder\subfolder\[filename.xls]ABC-"
comm = comm & day1 & "-" & mon & "-" & year1 & strCom

'the above comm and strcom are for worksheet naming

Sheet2.Cells(i, 5).Formula = comm

'Sheet2.Cells.Characters(
If InStr(1, Sheet2.Cells(i, 5).Value, "Error") 0 Then

Sheet2.Cells(i, 5) = ""
End If

Next

End Sub-

--
deepakmehta
------------------------------------------------------------------------
deepakmehta's Profile: http://www.excelforum.com/member.php...o&userid=29609
View this thread: http://www.excelforum.com/showthread...hreadid=493172

--

Dave Peterson

How to stop the 'select sheet' window pop up
 

The link you mentioned solved my problem....:)


--
deepakmehta
------------------------------------------------------------------------
deepakmehta's Profile: http://www.excelforum.com/member.php...o&userid=29609
View this thread: http://www.excelforum.com/showthread...hreadid=493172