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

jnf40,

Do you want to check for exactly 5 files in a particular folder? Is this a
shared workbook?

"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

Thanks for the question, I have been gone. No there can be numerous files in
this folder, and the folder will only be accessed by one person at a time.

"JRForm" wrote:

jnf40,

Do you want to check for exactly 5 files in a particular folder? Is this a
shared workbook?

"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