Well I haven't tried your code but my thinking is lets say there are 15
workbooks in the same folder and they open CSB 1257 Reports 12, CSB 1257
Reports 15 has not been opened and I don't want them to create another
workbook using the info in workbook CSB 1257 Reports 12, they would have to
open CSB 1257 Reports 15 to create another. I need some way to disable all of
the Commandbutton1 in all the workbooks 1 through 14 if they open them and
report 15 is still unopened.

"Joel" wrote:

I don't know why you are so concerned about the file names. My method works
with any valid filename and will work with any number of opened files.

Sub test()

For Each wkbk In Workbooks
'remove xls from filename
RawName = left(wkbk.name,instr(wkbk.name,".")-1)
select case RawName
case "CSB 1257 Reports 1"
commandcontrol1.disable
case "CSB 1257 Reports 2"
commandcontrol3.disable
case "CSB 1257 Reports 3"
commandcontrol3.disable
case "CSB 1257 Reports 4"
commandcontrol4.disable
case "CSB 1257 Reports 5"
commandcontrol5.disable
end select
Next wkbk
End Sub



"jnf40" wrote:

I understand what you are saying about the case "File A" and so on but what I
need is it to somehow check for the file using the iCtr or some other means
because the file names start with CSB 1257 Reports 1 and can go up from there
however many workbooks they create.

"Joel" wrote:

Sub test()

For Each wkbk In Workbooks
select case wkbk.Name
case "File A"
commandcontrol1.disable
case "File B"
commandcontrol3.disable
case "File C"
commandcontrol3.disable
case "File D"
commandcontrol4.disable
case "File E"
commandcontrol5.disable
end select
Next wkbk
End Sub



"jnf40" wrote:

Thanks, but I need to know if the workbook name that is open is the the last
one in the file or if it is one of the earlier ones. For instance if I have 5
workbooks in the folder I need to know if the workbook is Myfile 4 then I
need to disable a button on the userform that allows them to create another
workbook whereas if Myfile 5 is open then the button on the userform will be
enabled so they create a new workbook.


"Joel" wrote:

his code will find all the open workbooks

Sub test()

For Each wkbk In Workbooks
workbookname = wkbk.Name
If workbookname = "file 5" Then

'enter your code here
End If

Next wkbk
End Sub

"jnf40" wrote:

The following code creates the file for the workbook. I need to be able to
disable a form button if the person has a file open, lets say #4 and file #5
exists I do not want them to be able to create another workbook from file #4
they would have to open #5.
myParentFolder = "C:\"
On Error Resume Next
MkDir myParentFolder & mycsj
MkDir myParentFolder & mycsj & "\Pay Reports"
MkDir myParentFolder & mycsj & "\Pay Reports" & "\" &
Range("name")
On Error GoTo 0
myFolder = myParentFolder & mycsj & "\Pay Reports" & "\" &
Range("name")
i = 1
myFileName = Range("file")
While Dir(myFolder & "\" & myFileName & " " & i & ".xls") < ""
i = i + 1
Wend
myFileName = myFolder & "\" & myFileName & " " & i & ".xls"
ActiveWorkbook.SaveAs Filename:= _
myFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
MsgBox "File Saved to " & myFileName
End If