I have thought about your problem a lot. Diasabling the button is a good
idea but it is not fool-proof. Somebody on another PC could open the file
and you would not cause the button to be disabled.

You need to assign a unique filename to each button. Button will only be
enabled when the filename corresponding to the button doesn't exist.

The fool-proof method is when the button is clicked to check the files in
the folder to make sure the file doesn't exist and create a new file
immediately so two people aren't working with temporary files with the same
name.

You can use a workbook open function to check if the files exist in the
folder and disable the buttons for files that already exist when the workbook
is opened.

If you need to keep track of the number of opened files you can add a count
to my code to count the files.

"jnf40" wrote:

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