File in Use
 

Hello everyone. I have a excel file that can be downloaded from my site. The
sheet contains a macro that runs onload and a button that calls the same
macro (if the user wants to run it again) they all run fine as long as the
user actually saves the file to his comp. If the user just says to open the
file the onload macro runs fine once, but when the button that runs the same
macro again is clicked the user recieves a file in use error, if they select
to run the read only version the macro cannot be found and then the vb stops
and you receive a error to debug or end. Is there anyway to stops this. I
really, really need some help to figure this one out as soons as possible.
Thanks in advance.

File in Use
 

How about forcing a save by putting code like this at the beginning of that
macro:

If Len(ThisWorkbook.Path) = 0 Then
MsgBox "You will need to save this file somewhere first!", _
vbExclamation, _
"running macro"
Exit Sub
End If

RBS


"Armando" wrote in message
...
Hello everyone. I have a excel file that can be downloaded from my site.
The
sheet contains a macro that runs onload and a button that calls the same
macro (if the user wants to run it again) they all run fine as long as the
user actually saves the file to his comp. If the user just says to open
the
file the onload macro runs fine once, but when the button that runs the
same
macro again is clicked the user recieves a file in use error, if they
select
to run the read only version the macro cannot be found and then the vb
stops
and you receive a error to debug or end. Is there anyway to stops this. I
really, really need some help to figure this one out as soons as possible.
Thanks in advance.

File in Use
 

"RB Smissaert" wrote:

How about forcing a save by putting code like this at the beginning of that
macro:

If Len(ThisWorkbook.Path) = 0 Then
MsgBox "You will need to save this file somewhere first!", _
vbExclamation, _
"running macro"
Exit Sub
End If

RBS


"Armando" wrote in message
...
Hello everyone. I have a excel file that can be downloaded from my site.
The
sheet contains a macro that runs onload and a button that calls the same
macro (if the user wants to run it again) they all run fine as long as the
user actually saves the file to his comp. If the user just says to open
the
file the onload macro runs fine once, but when the button that runs the
same
macro again is clicked the user recieves a file in use error, if they
select
to run the read only version the macro cannot be found and then the vb
stops
and you receive a error to debug or end. Is there anyway to stops this. I
really, really need some help to figure this one out as soons as possible.
Thanks in advance.

Thanks for the advice, I really appreciate it. But is there anyway to allow the macros to run without forceing a save?

File in Use
 

Not the way your code is written.
It is expecting something that involves a save.
Rewrite your code so this requirement is no longer applies.

NickHK

"Armando" wrote in message
...


"RB Smissaert" wrote:

How about forcing a save by putting code like this at the beginning of
that
macro:

If Len(ThisWorkbook.Path) = 0 Then
MsgBox "You will need to save this file somewhere first!", _
vbExclamation, _
"running macro"
Exit Sub
End If

RBS


"Armando" wrote in message
...
Hello everyone. I have a excel file that can be downloaded from my
site.
The
sheet contains a macro that runs onload and a button that calls the
same
macro (if the user wants to run it again) they all run fine as long as
the
user actually saves the file to his comp. If the user just says to
open
the
file the onload macro runs fine once, but when the button that runs
the
same
macro again is clicked the user recieves a file in use error, if they
select
to run the read only version the macro cannot be found and then the vb
stops
and you receive a error to debug or end. Is there anyway to stops
this. I
really, really need some help to figure this one out as soons as
possible.
Thanks in advance.

Thanks for the advice, I really appreciate it. But is there anyway to
allow the macros to run without forceing a save?

File in Use
 

Hopefully this will help you guys look at what's going on. The code that I am
using creates a command button and assigns my macro(Opt) to the button. The
code is performed onload because the tab is created by a query, so the button
cannot just be created on the excel template and assigned the macro.
Hopefully looking at the code my help you guys help me. Once again thanks a
lot everyone for your help.

Code:

Public Sub AddButtonToSheet()

Dim oFormatSelection As Object
Dim rFormatActive As Range
Dim sCaption As String
Set oFormatSelection = Selection

' checks to see if a button was already created
If Worksheets("Report Data").Shapes.Count 0 Then
Exit Sub
End If

' creates the format options button, since the report data tab does not
exist in the template
If TypeName(oFormatSelection) = "Range" Then _
Set rFormatActive = ActiveCell
Application.ScreenUpdating = False
With Worksheets("Report Data").Shapes.AddFormControl( _
xlButtonControl, 0, 31, 150, 18)
..ControlFormat.PrintObject = False
With .TextFrame.Characters.Font
..Name = "Arial"
..FontStyle = "Regular"
..Size = 10
..ColorIndex = 0
..Shadow = False
End With
..Select
Selection.OnAction = "Opt"
sCaption = " Format Options "
If Len(sCaption) 0 Then _
Selection.Caption = sCaption
Selection.AutoSize = False
Selection.Placement = xlFreeFloating
End With
oFormatSelection.Select
If Not rFormatActive Is Nothing Then rFormatActive.Activate
Application.ScreenUpdating = True

End Sub


"NickHK" wrote:

Not the way your code is written.
It is expecting something that involves a save.
Rewrite your code so this requirement is no longer applies.

NickHK

"Armando" wrote in message
...


"RB Smissaert" wrote:

How about forcing a save by putting code like this at the beginning of
that
macro:

If Len(ThisWorkbook.Path) = 0 Then
MsgBox "You will need to save this file somewhere first!", _
vbExclamation, _
"running macro"
Exit Sub
End If

RBS


"Armando" wrote in message
...
Hello everyone. I have a excel file that can be downloaded from my
site.
The
sheet contains a macro that runs onload and a button that calls the
same
macro (if the user wants to run it again) they all run fine as long as
the
user actually saves the file to his comp. If the user just says to
open
the
file the onload macro runs fine once, but when the button that runs
the
same
macro again is clicked the user recieves a file in use error, if they
select
to run the read only version the macro cannot be found and then the vb
stops
and you receive a error to debug or end. Is there anyway to stops
this. I
really, really need some help to figure this one out as soons as
possible.
Thanks in advance.

Thanks for the advice, I really appreciate it. But is there anyway to
allow the macros to run without forceing a save?