It works when I step through it but it won't run

[chillihawk]

Hello Everyone,

I have a Sub in an add-in which when called (via a keyboard shortcut
CTRL+SHIFT+G) opens an existing workbook, gets some data out of it and
puts that data on the sheet. The function is driven by data in the
comment of the Activecell. Now it works when I step through it, it
works when I run through it (ie hit F5), (presuming in both cases the
activecell is the correct one) but it will not execute properly in
response to the keyboard shortcut. It stops after opening the existing
workbook. Here is the offending code:

For k = 1 To UBound(s, 1)
Set wbk = Application.Workbooks.Open(fileName:=s(k), AddtoMru:=False,
IgnoreReadOnlyRecommended:=True)
If (wbk Is Nothing) Then Err.Raise vbError + 2001, FUNCTION_NAME,
"Unable to open correlation source."
i = j
With wbk
..IsAddin = True
While (vToGet(i, 3) = s(k))
vToGet(i, 6) = wbk.Sheets("grid").Range(oMyTx.getAsXLColumn(vToGet(i,
5) + 2) & CStr(vToGet(i, 4))).Value
i = i + 1
Wend
subscriptStart:
j = i - 1
End With
wbk.Close SaveChanges:=False
Set wbk = Nothing
Next k

wbk is declared: Dim wbk as Workbook

I don't know if a reference to wbk is ever set, it seems like it isn't
but I can't tell why. When execution stops, the workbook (ie s(k) in
the above code) is open and active and when I press ALT + F11 to go
into the debugger it is sometimes in design mode and sometimes not.

Can anyone shed any light on this problem.

Thanks everyone,

chilli

[chillihawk]

Hello Everyone,

Some more information on this one. I've changed the keyboard shortcut
which launches the sub with the following results:

new shortcut: CTRL+l
result: works great

new shortcut: CTRL+SHIFT+L
result: doesn't work

Now that I have a solution the urgency has abated but if anyone has
experienced anything similar or knows why this has happened I'd like to
hear about it.

Thanks everyone,

chilli

chillihawk wrote:
> Hello Everyone,
>
> I have a Sub in an add-in which when called (via a keyboard shortcut
> CTRL+SHIFT+G) opens an existing workbook, gets some data out of it and
> puts that data on the sheet. The function is driven by data in the
> comment of the Activecell. Now it works when I step through it, it
> works when I run through it (ie hit F5), (presuming in both cases the
> activecell is the correct one) but it will not execute properly in
> response to the keyboard shortcut. It stops after opening the existing
> workbook. Here is the offending code:
>
> For k = 1 To UBound(s, 1)
> Set wbk = Application.Workbooks.Open(fileName:=s(k), AddtoMru:=False,
> IgnoreReadOnlyRecommended:=True)
> If (wbk Is Nothing) Then Err.Raise vbError + 2001, FUNCTION_NAME,
> "Unable to open correlation source."
> i = j
> With wbk
> .IsAddin = True
> While (vToGet(i, 3) = s(k))
> vToGet(i, 6) = wbk.Sheets("grid").Range(oMyTx.getAsXLColumn(vToGet(i,
> 5) + 2) & CStr(vToGet(i, 4))).Value
> i = i + 1
> Wend
> subscriptStart:
> j = i - 1
> End With
> wbk.Close SaveChanges:=False
> Set wbk = Nothing
> Next k
>
> wbk is declared: Dim wbk as Workbook
>
> I don't know if a reference to wbk is ever set, it seems like it isn't
> but I can't tell why. When execution stops, the workbook (ie s(k) in
> the above code) is open and active and when I press ALT + F11 to go
> into the debugger it is sometimes in design mode and sometimes not.
>
> Can anyone shed any light on this problem.
>
> Thanks everyone,
>
> chilli

[Dave Peterson]

Remove the Shift from the shortcut combination.

chillihawk wrote:
>
> Hello Everyone,
>
> I have a Sub in an add-in which when called (via a keyboard shortcut
> CTRL+SHIFT+G) opens an existing workbook, gets some data out of it and
> puts that data on the sheet. The function is driven by data in the
> comment of the Activecell. Now it works when I step through it, it
> works when I run through it (ie hit F5), (presuming in both cases the
> activecell is the correct one) but it will not execute properly in
> response to the keyboard shortcut. It stops after opening the existing
> workbook. Here is the offending code:
>
> For k = 1 To UBound(s, 1)
> Set wbk = Application.Workbooks.Open(fileName:=s(k), AddtoMru:=False,
> IgnoreReadOnlyRecommended:=True)
> If (wbk Is Nothing) Then Err.Raise vbError + 2001, FUNCTION_NAME,
> "Unable to open correlation source."
> i = j
> With wbk
> .IsAddin = True
> While (vToGet(i, 3) = s(k))
> vToGet(i, 6) = wbk.Sheets("grid").Range(oMyTx.getAsXLColumn(vToGet(i,
> 5) + 2) & CStr(vToGet(i, 4))).Value
> i = i + 1
> Wend
> subscriptStart:
> j = i - 1
> End With
> wbk.Close SaveChanges:=False
> Set wbk = Nothing
> Next k
>
> wbk is declared: Dim wbk as Workbook
>
> I don't know if a reference to wbk is ever set, it seems like it isn't
> but I can't tell why. When execution stops, the workbook (ie s(k) in
> the above code) is open and active and when I press ALT + F11 to go
> into the debugger it is sometimes in design mode and sometimes not.
>
> Can anyone shed any light on this problem.
>
> Thanks everyone,
>
> chilli

--

Dave Peterson

[Dave Peterson]

If you hold down the shift key when you open a workbook, then the auto_open and
workbook_open routines are skipped.

It seems that the shift key in the shortcut key combination has enough of the
same effect to confuse excel and it stops running the macro.

chillihawk wrote:
>
> Hello Everyone,
>
> Some more information on this one. I've changed the keyboard shortcut
> which launches the sub with the following results:
>
> new shortcut: CTRL+l
> result: works great
>
> new shortcut: CTRL+SHIFT+L
> result: doesn't work
>
> Now that I have a solution the urgency has abated but if anyone has
> experienced anything similar or knows why this has happened I'd like to
> hear about it.
>
> Thanks everyone,
>
> chilli
>
> chillihawk wrote:
> > Hello Everyone,
> >
> > I have a Sub in an add-in which when called (via a keyboard shortcut
> > CTRL+SHIFT+G) opens an existing workbook, gets some data out of it and
> > puts that data on the sheet. The function is driven by data in the
> > comment of the Activecell. Now it works when I step through it, it
> > works when I run through it (ie hit F5), (presuming in both cases the
> > activecell is the correct one) but it will not execute properly in
> > response to the keyboard shortcut. It stops after opening the existing
> > workbook. Here is the offending code:
> >
> > For k = 1 To UBound(s, 1)
> > Set wbk = Application.Workbooks.Open(fileName:=s(k), AddtoMru:=False,
> > IgnoreReadOnlyRecommended:=True)
> > If (wbk Is Nothing) Then Err.Raise vbError + 2001, FUNCTION_NAME,
> > "Unable to open correlation source."
> > i = j
> > With wbk
> > .IsAddin = True
> > While (vToGet(i, 3) = s(k))
> > vToGet(i, 6) = wbk.Sheets("grid").Range(oMyTx.getAsXLColumn(vToGet(i,
> > 5) + 2) & CStr(vToGet(i, 4))).Value
> > i = i + 1
> > Wend
> > subscriptStart:
> > j = i - 1
> > End With
> > wbk.Close SaveChanges:=False
> > Set wbk = Nothing
> > Next k
> >
> > wbk is declared: Dim wbk as Workbook
> >
> > I don't know if a reference to wbk is ever set, it seems like it isn't
> > but I can't tell why. When execution stops, the workbook (ie s(k) in
> > the above code) is open and active and when I press ALT + F11 to go
> > into the debugger it is sometimes in design mode and sometimes not.
> >
> > Can anyone shed any light on this problem.
> >
> > Thanks everyone,
> >
> > chilli

--

Dave Peterson

[Pflugs]

I have the same problem with a macro I wrote. The macro starts using a Word
app, opens a text file, formats it, opens Excel, imports the file, formats
again, copy 3 modules into the target, and finally closes the text file and
saves the spreadsheet. When I step through it, it works fine, but when I run
it (from Ctrl+r), the code skips the Excel part or the copying modules part
all together. What could cause this?

Pflugs

"Dave Peterson" wrote:

> If you hold down the shift key when you open a workbook, then the auto_open and
> workbook_open routines are skipped.
>
> It seems that the shift key in the shortcut key combination has enough of the
> same effect to confuse excel and it stops running the macro.
>
> chillihawk wrote:
> >
> > Hello Everyone,
> >
> > Some more information on this one. I've changed the keyboard shortcut
> > which launches the sub with the following results:
> >
> > new shortcut: CTRL+l
> > result: works great
> >
> > new shortcut: CTRL+SHIFT+L
> > result: doesn't work
> >
> > Now that I have a solution the urgency has abated but if anyone has
> > experienced anything similar or knows why this has happened I'd like to
> > hear about it.
> >
> > Thanks everyone,
> >
> > chilli
> >
> > chillihawk wrote:
> > > Hello Everyone,
> > >
> > > I have a Sub in an add-in which when called (via a keyboard shortcut
> > > CTRL+SHIFT+G) opens an existing workbook, gets some data out of it and
> > > puts that data on the sheet. The function is driven by data in the
> > > comment of the Activecell. Now it works when I step through it, it
> > > works when I run through it (ie hit F5), (presuming in both cases the
> > > activecell is the correct one) but it will not execute properly in
> > > response to the keyboard shortcut. It stops after opening the existing
> > > workbook. Here is the offending code:
> > >
> > > For k = 1 To UBound(s, 1)
> > > Set wbk = Application.Workbooks.Open(fileName:=s(k), AddtoMru:=False,
> > > IgnoreReadOnlyRecommended:=True)
> > > If (wbk Is Nothing) Then Err.Raise vbError + 2001, FUNCTION_NAME,
> > > "Unable to open correlation source."
> > > i = j
> > > With wbk
> > > .IsAddin = True
> > > While (vToGet(i, 3) = s(k))
> > > vToGet(i, 6) = wbk.Sheets("grid").Range(oMyTx.getAsXLColumn(vToGet(i,
> > > 5) + 2) & CStr(vToGet(i, 4))).Value
> > > i = i + 1
> > > Wend
> > > subscriptStart:
> > > j = i - 1
> > > End With
> > > wbk.Close SaveChanges:=False
> > > Set wbk = Nothing
> > > Next k
> > >
> > > wbk is declared: Dim wbk as Workbook
> > >
> > > I don't know if a reference to wbk is ever set, it seems like it isn't
> > > but I can't tell why. When execution stops, the workbook (ie s(k) in
> > > the above code) is open and active and when I press ALT + F11 to go
> > > into the debugger it is sometimes in design mode and sometimes not.
> > >
> > > Can anyone shed any light on this problem.
> > >
> > > Thanks everyone,
> > >
> > > chilli
>
> --
>
> Dave Peterson
>

[Dave Peterson]

I think it would be pretty difficult to guess without seeing the code--or at
least a skinnied down version of the code.

Pflugs wrote:
>
> I have the same problem with a macro I wrote. The macro starts using a Word
> app, opens a text file, formats it, opens Excel, imports the file, formats
> again, copy 3 modules into the target, and finally closes the text file and
> saves the spreadsheet. When I step through it, it works fine, but when I run
> it (from Ctrl+r), the code skips the Excel part or the copying modules part
> all together. What could cause this?
>
> Pflugs
>
> "Dave Peterson" wrote:
>
> > If you hold down the shift key when you open a workbook, then the auto_open and
> > workbook_open routines are skipped.
> >
> > It seems that the shift key in the shortcut key combination has enough of the
> > same effect to confuse excel and it stops running the macro.
> >
> > chillihawk wrote:
> > >
> > > Hello Everyone,
> > >
> > > Some more information on this one. I've changed the keyboard shortcut
> > > which launches the sub with the following results:
> > >
> > > new shortcut: CTRL+l
> > > result: works great
> > >
> > > new shortcut: CTRL+SHIFT+L
> > > result: doesn't work
> > >
> > > Now that I have a solution the urgency has abated but if anyone has
> > > experienced anything similar or knows why this has happened I'd like to
> > > hear about it.
> > >
> > > Thanks everyone,
> > >
> > > chilli
> > >
> > > chillihawk wrote:
> > > > Hello Everyone,
> > > >
> > > > I have a Sub in an add-in which when called (via a keyboard shortcut
> > > > CTRL+SHIFT+G) opens an existing workbook, gets some data out of it and
> > > > puts that data on the sheet. The function is driven by data in the
> > > > comment of the Activecell. Now it works when I step through it, it
> > > > works when I run through it (ie hit F5), (presuming in both cases the
> > > > activecell is the correct one) but it will not execute properly in
> > > > response to the keyboard shortcut. It stops after opening the existing
> > > > workbook. Here is the offending code:
> > > >
> > > > For k = 1 To UBound(s, 1)
> > > > Set wbk = Application.Workbooks.Open(fileName:=s(k), AddtoMru:=False,
> > > > IgnoreReadOnlyRecommended:=True)
> > > > If (wbk Is Nothing) Then Err.Raise vbError + 2001, FUNCTION_NAME,
> > > > "Unable to open correlation source."
> > > > i = j
> > > > With wbk
> > > > .IsAddin = True
> > > > While (vToGet(i, 3) = s(k))
> > > > vToGet(i, 6) = wbk.Sheets("grid").Range(oMyTx.getAsXLColumn(vToGet(i,
> > > > 5) + 2) & CStr(vToGet(i, 4))).Value
> > > > i = i + 1
> > > > Wend
> > > > subscriptStart:
> > > > j = i - 1
> > > > End With
> > > > wbk.Close SaveChanges:=False
> > > > Set wbk = Nothing
> > > > Next k
> > > >
> > > > wbk is declared: Dim wbk as Workbook
> > > >
> > > > I don't know if a reference to wbk is ever set, it seems like it isn't
> > > > but I can't tell why. When execution stops, the workbook (ie s(k) in
> > > > the above code) is open and active and when I press ALT + F11 to go
> > > > into the debugger it is sometimes in design mode and sometimes not.
> > > >
> > > > Can anyone shed any light on this problem.
> > > >
> > > > Thanks everyone,
> > > >
> > > > chilli
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson