vanishing buttons / follow-on question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ron.Winkley@gmail.com - 06 Dec 2007 23:33 GMT

That was nicely resolved as noted, but:

Example, one of my macros starts like this:
===========================
Sub MainLogFindLastEntry()
'
' MainLogFindLastEntry Macro
' Macro recorded 29 05 2006 by Consultant
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Sheets("Log").Select
===========================
now that this macro is called by a button, I have discovered the
following:

1. if no worksheets are open the macro button will find a and open the
correct workbook and sheet [brilliant!]
2. if other sheet(s), workbook(s) are open but not the correct
workbook, the macro crashes . . .

Can I include in the macro the name of the excel file that needs to be
opened, to fix the above problem [2] ?
Please can you tell me, what is the macro command / syntax to do this?

Thanks,

RonW.

Reply to this Message

Dave Peterson - 06 Dec 2007 23:59 GMT

That button that you clicked on has a macro assigned to it.

If the workbook that contains that macro is not open, then excel will open it to
run the macro.

And I'm betting that your workbook is a normal .xls workbook. When you (or
excel) open it, then that workbook is the active workbook and the
"sheets("log").select" line can work nicely.

If the macro workbook is already open, then:
sheets("log").select
will try to select the log worksheet of the active workbook. If the active
workbook doesn't have a worksheet by that name, then that line of code will
fail.

If you want to select the Log sheet in the workbook that owns the code, you
could use these two lines instead:

thisworkbook.activate
sheets("log").select

Or just a single line:

application.goto thisworkbook.sheets("log").range("a1"), scroll:=true

> That was nicely resolved as noted, but:
>
[quoted text clipped - 24 lines]
>
> RonW.

Signature

Dave Peterson

Reply to this Message

Rate this thread: