Creating menu items without using VBA way?

[StargateFan]

It's been a rough few days! I've read so much that it starting to
blur and run together! <lol>.

Anyway, the pressures of the demands at work mean that I must find
easier ways to do things to help ease the load. I stumbled on some
pages on creating custom pulldown menus but found they were VBA-based
ones for the most part (like the menumaker file example with
downloadable file here:
http://www.j-walk.com/ss/excel/tips/tip53.htm). I've not had much
success with that because although the example file is there, I just
don't know enough vb to make it work. First off, I don't need
sub-menus and I don't need as many different types of items either -
so right from there, I'm deviating a lot from the example file. I get
error messages right away.

Then I was lucky enough to stumble on to a manual way of making menus
that works like customizing toolbars, something I am extremely
familiar with: http://www.cpearson.com/excel/menus.htm

The recent problems at work mean that I need to constantly be
accessing about 20 files found on different folders on the network. I
have shortcuts to the two major ones in my QL but it's getting really
old, really fast to have to click through shortcuts elsewhere to get
to the rest. It would be easiest if all these files were available in
the menu I created called "MyDocts". I was able to add the pulldown
in a heartbet following the instructions on the above manual method
webpage, but I just can't seem to figure out how to get items onto the
menu! The terminology is confusing to me, a newbie to menu issues,
but I believe I figured out what the meanings are. From that page
above, http://www.cpearson.com/excel/menus.htm :

"The procedure for adding a new tool to a command bar is the same,
except that you should choose Custom Button rather than Custom Menu
Item from the Macros Categories list in the Customize dialog box."

I've tried choosing custom button as it says. But I guess that what I
do next is wrong. Like customizing the toolbar, we usu. just drag the
item. When I try to drag the custom icon to the new "MyDocts" menu,
all I achieve is creating another custom menu, this time with a happy
face! <lol>

If I can just get the happy face one to appear as what is available
underneath the "MyDocts" menu, I'd have it made. I could then take it
from there to assign a macro to each entry. At least, that's what I'm
hoping we can do because the other VB way just is so complicated in
comparison for me.

Thanks so much!

 

[Jan Karel Pieterse]

Hi StargateFan,

The recent problems at work mean that I need to constantly be
accessing about 20 files found on different folders on the network.

That calls for this fine utility:

ftp://ftp.mcgimpsey.com/excel/workmenu.xla

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[StargateFan]

Hi StargateFan,


That calls for this fine utility:

ftp://ftp.mcgimpsey.com/excel/workmenu.xla

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Excel-lent!!!! That works so beautifully.

Thanks so much!

 

[StargateFan]

This is such an awesome utility! It will do the job very
well. But I did get runtime errors, both at home and here
at the office. I don't remember if the runtime error was
the same at home for certain, but I think it is and it's
unlikely that 2 different OSs would necessarily call 2
different errors just because of OS, no?

The error is this:

"Run-time error '1004':

Method 'Rows' of object'_Global' failed"

And when the debugger opens, there's little yellow arrow
and this line of coding is highlighted in yellow:

" Set workRange = .Range("A2", .Range("A" &
Rows.Count).End(xlUp))"

Hopefully someone know what the problem might be.

The sheets I've added to the "Work" menu don't show up
though they are there. I know this because when I thought
it's because a book hadn't taken, I tried entering it
again and I got the msg that book was already present.

The only time the menu appears at this point is when I add
a new one. But as soon as I exit XL2K, then it won't work
again properly the next time.

Thanks so much for this! Once someone can recommend a
fix, this will work just perfectly!

D

 

[Jones]

[Well, it was easy to sign up for Google. No more messing around with
the MS ng site!! Sheesh, you can type in word-for-word what is in the
subject line, etc., and the ms ng site just doesn't find anything!
Phew! <g>]

I posted a msg earlier to that site but it's not appearing so don't
know what or even if I did something wrong. Best to post again.

I applied the xla doct and it initially does a very fantastic job.
But I repeatedly on opening Excel get this runtime error:

"Run-time error '1004':

Method 'Rows' of object'_Global' failed"

Also, the docts don't show up now in the new "Work" pulldown menu.
They're there, as evidenced by msg rejecting addition of same books
where it says the book is already on the menu even though it doesn't
appear. However, I do see the full list whenever I have added a new
doct; but, it disappears after closing XL2K because next time I launch
XL, I get the 1004 error again.

The debugger highlights in yellow this line:

" Set workRange = .Range("A2", .Range("A" &
Rows.Count).End(xlUp))"

I'm hoping that someone will know what the coding error is and can let
me know.

Thank you!!

****************************************

 

[Dave Peterson]

Set workRange = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
(extra period in front of the .rows.count

 

[StargateFan]

Set workRange = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
(extra period in front of the .rows.count

Excellent! Phew, I was getting worried. Thought I was doing
something wrong. But then after coming home and seeing that I was
getting exactly the same thing here re the runtime error, I knew the
problem had to be in coding. No more error now!

-*-*-*-*-*-*-*

Turns out, though, that there is another glitch. I'm hoping we can
get this last thing fixed. This is a totally awesome function to have
that if this next thing can be fixed, it'll be perfect. At least,
from the looks of things now <g>!

What it also doesn't do is keep the references properly. Every time I
start up Excel again, a deleted filename reference re-appears and only
the first item I added this morning appears. But I stumbled upon
something - when I choose the "delete item" from the menu and then
choose "cancel" to close the delete dialogue box, everything shows as
it should. Yet each time when I close Excel, the same thing happens
all over again. It goes back to a third state after re-installing
this add-in this morning - one file of the originals deleted, and the
first one I added in appears.

Yup, I'm about at try #10 and this still is happening. It's like the
"delete item" > "cancel" forces the add-in to re-calculate/refresh
somehow because I can hear the hdd revving up each time. Then the
next time I click on the "Work" menu, it's okay and showing all the
references it should.

Sounds like to me, with my very limited knowledge of course, that
there is a "recalculation" or "refresh" code missing in there
somehwere (???).

Thanks so much for the help. I wish I could say I could live with
this glitch, but the unfortunate thing is that I close/start Excel
many times a day at the office and certainly don't keep it open all
the time here at home either. I use Excel only when I need it then
shut down. So it's not like this type of behaviour wouldn't be a
major pain! <g>

Cheers!

 

[StargateFan]

[snip]

What it also doesn't do is keep the references properly. Every time I
start up Excel again, a deleted filename reference re-appears and only
the first item I added this morning appears. But I stumbled upon
something - when I choose the "delete item" from the menu and then
choose "cancel" to close the delete dialogue box, everything shows as
it should. Yet each time when I close Excel, the same thing happens
all over again. It goes back to a third state after re-installing
this add-in this morning - one file of the originals deleted, and the
first one I added in appears.

[snip]

It's like the

"delete item" > "cancel" forces the add-in to re-calculate/refresh
somehow because I can hear the hdd revving up each time. Then the
next time I click on the "Work" menu, it's okay and showing all the
references it should.

Sounds like to me, with my very limited knowledge of course, that
there is a "recalculation" or "refresh" code missing in there
somehwere (???).

[snip]

Anyone know why this might be the case and how to fix?

Thanks!

 

[Dave Peterson]

I tried getting a copy from JE's site. But I couldn't open the newly downloaded
file successfully. (It worked a couple of days ago (IIRC), though.)

But on his site: http://www.mcgimpsey.com/
there's a link called Contact.

You may want to ask JE directly.

[snip]

What it also doesn't do is keep the references properly. Every time I
start up Excel again, a deleted filename reference re-appears and only
the first item I added this morning appears. But I stumbled upon
something - when I choose the "delete item" from the menu and then
choose "cancel" to close the delete dialogue box, everything shows as
it should. Yet each time when I close Excel, the same thing happens
all over again. It goes back to a third state after re-installing
this add-in this morning - one file of the originals deleted, and the
first one I added in appears.

[snip]

It's like the

"delete item" > "cancel" forces the add-in to re-calculate/refresh
somehow because I can hear the hdd revving up each time. Then the
next time I click on the "Work" menu, it's okay and showing all the
references it should.

Sounds like to me, with my very limited knowledge of course, that
there is a "recalculation" or "refresh" code missing in there
somehwere (???).

[snip]

Anyone know why this might be the case and how to fix?

Thanks!

 

[StargateFan]

I tried getting a copy from JE's site. But I couldn't open the newly downloaded
file successfully. (It worked a couple of days ago (IIRC), though.)

But on his site: http://www.mcgimpsey.com/
there's a link called Contact.

You may want to ask JE directly.

Actually, the link worked just fine now for me. Maybe it's been
fixed.

Thanks re tip.

Thanks.

[snip]

What it also doesn't do is keep the references properly. Every time I
start up Excel again, a deleted filename reference re-appears and only
the first item I added this morning appears. But I stumbled upon
something - when I choose the "delete item" from the menu and then
choose "cancel" to close the delete dialogue box, everything shows as
it should. Yet each time when I close Excel, the same thing happens
all over again. It goes back to a third state after re-installing
this add-in this morning - one file of the originals deleted, and the
first one I added in appears.

[snip]

It's like the

"delete item" > "cancel" forces the add-in to re-calculate/refresh
somehow because I can hear the hdd revving up each time. Then the
next time I click on the "Work" menu, it's okay and showing all the
references it should.

Sounds like to me, with my very limited knowledge of course, that
there is a "recalculation" or "refresh" code missing in there
somehwere (???).

[snip]

Anyone know why this might be the case and how to fix?

Thanks!

 

[StargateFan]

I tried getting a copy from JE's site. But I couldn't open the newly downloaded
file successfully. (It worked a couple of days ago (IIRC), though.)

But on his site: http://www.mcgimpsey.com/
there's a link called Contact.

You may want to ask JE directly.

I emailed directly and JE said this file had been forgotten and needed
re-working so it got pulled! Sometimes one achieves the wrong result
<g>! Anyway, lucky for me, I figured out how to make the menus the
non-VBA way. Turns out it was sooo easy, as I was hoping.

Apparently the file will go up again at some point though I have no
details.

Cheers!

[snip]

Anyone know why this might be the case and how to fix?

Thanks!

 

[AA]

I figured out how to make the menus the

non-VBA way.

But no "Work" menu, ala Word?

 

[StargateFan]

But no "Work" menu, ala Word?

You lost me. <g> What is your question?

 

[AA]

I figured out how to make the menus the

You lost me. <g> What is your question?

The utility mentioned earlier in the thread:

ftp://ftp.mcgimpsey.com/excel/workmenu.xla

created a Work Menu in Excel, like the one in Word.

In case you don't have Word, or haven't stumbled across the Work menu,
it allows you to add an open file to a menu list, where it stays until
you decide to remove it. Kind of like the Most Recently Used File
List, except the user is in control. Very handy.

 

[StargateFanFromWork]

The utility mentioned earlier in the thread:

ftp://ftp.mcgimpsey.com/excel/workmenu.xla

created a Work Menu in Excel, like the one in Word.

This has been pulled from the site temporarily. When I emailed them, I got
a response that they'd forgotten that file was there but also that they knew
that it had some glitches but that it would be repaired (sorry, I did
mention this but I might not have been clear or it might not have been in
this thread).

My understanding is that when the file is fixed, it will be made available
again, however, I wasn't given a time frame.

This is an add-in, as you know. The advantage of creating a menu without
VBA scripting is that it just gets saved to the toolbar excel.xlb file which
means there's one less file to back up. Also, what I liked about the manual
technique is that I can arrange the files as I like very easily.

I'll be in touch with them again re the add-in and will get back to the
group once I have a response.

In case you don't have Word, or haven't stumbled across the Work menu,
it allows you to add an open file to a menu list, where it stays until
you decide to remove it. Kind of like the Most Recently Used File
List, except the user is in control. Very handy.

Oh, so this will work with Word, too?? I'll have to give it a try. I only
used it on Excel but it has 2 major glitches with it.

Unless you're speaking of another add-in?? If so, can you provide a link to
that?

Thanks!

 

[Andy]

Also, what I liked about the manual technique is that I can

arrange the files as I like very easily.

How do you get filenames on the Custom Menu Item?

Oh, so this will work with Word, too?? I'll have to give it a
try. I only used it on Excel but it has 2 major glitches with it.

Unless you're speaking of another add-in?? If so, can you provide
a link to that?

It's not an add-in, it's a built in Word menu. But it's not on
the menu bar by default, you have to put it there. In the Customize
dialog, under Built In Menus, it's the Work menu.

But there's no built-in way to get files *off* the menu. I put the
"Remove Menu Shortcut" command right on the Work menu. But watch out
for that bold minus sign! It's dangerous! It will remove anything
from a menu, not just filenames from the Work menu.

 

[StargateFan]

How do you get filenames on the Custom Menu Item?

After putting the NEW MENU item along the top, I right-clicked and saw
there was a spot to rename just like in a toolbar entry. So this is
process: I right-click on any toolbar and select customize (or you
can click TOOLS > CUSTOMIZE). From the commands tab, under categories
box I'll scroll down to choose NEW MENU and then in the Commands
window will drag up the NEW MENU option. Once the NEW MENU appears
along the top, I'll right-blick on it and where it says name, the
default NEW NAME appears. I'll just type over that and put something
like "My Doc&uments". The ampersand, &, as you probably know, gives
one keyboard access to this command so that one could also just press
Alt then "u" to this menu.

Then I've added items by choosing MACROS from the commands window and
dragging up the CUSTOM BUTTON smiley face to the new menu which when
you drag near it, pops up a square box just ready to receive entries!

 

[Andy]

Also, what I liked about the manual technique is that I can

How do you get filenames on the Custom Menu Item?

I guess I answered my own question, which is to assign a hyperlink to
the Custom Menu Item pointing to the filename.

The only problem is that when the file loads, it causes the #%$#@# Web
toolbar to be displayed, even though I put

Application.CommandBars("Web").Visible = False

in my Workbook_Open macro.

So I put a button on the Web toolbar to quickly close it.

 

[StargateFanFromWork]

I guess I answered my own question, which is to assign a hyperlink to
the Custom Menu Item pointing to the filename.

The only problem is that when the file loads, it causes the #%$#@# Web
toolbar to be displayed, even though I put

Application.CommandBars("Web").Visible = False

in my Workbook_Open macro.

So I put a button on the Web toolbar to quickly close it.

I got around that. I finally docked the web toolbar in a position where
when it loaded, it wouldn't be noticeable anyway and then have it open at
all time. Since I never use the web toolbar, I ended up taking everything
off that was on it and putting everything that was on one of my own toolbars
there. So now I don't see that behaviour. I'm sure it's still happening in
the background, but my chosen toolbar is still there albeit it's no longer
one that has my name on it! <g>

If I can't go through something, I find a legitimate way to go under, over
or around! <lol>

 

[Andy]

I got around that. I finally docked the web toolbar in a position where

when it loaded, it wouldn't be noticeable anyway and then have it open at
all time. Since I never use the web toolbar, I ended up taking everything
off that was on it and putting everything that was on one of my own toolbars
there. So now I don't see that behaviour. I'm sure it's still happening in
the background, but my chosen toolbar is still there albeit it's no longer
one that has my name on it! <g>

hmmm, not bad....