File in Use

[Armando]

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.

[RB Smissaert]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

[Armando]

"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?

[NickHK]

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" <[email protected]> wrote in message
news:[email protected]...
>
>
> "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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?

[Armando]

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" <[email protected]> wrote in message
> news:[email protected]...
> >
> >
> > "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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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?
>
>
>