Command button position problem in excel

scoochie · 01-10-2006, 06:36 AM

I am new to using the VBA capabilities of Excel. I recently created a spreadsheet in which I have created a "console" of 27 command buttons that appear at the top of the sheet. I have placed them 9 wide by 3 high and assigned them various hyperlink functions through VBA to perform email, web and network folder hyperlinks by clicking on the associated command button. My programming seems to work fine as far as functioning as intended when I test out of design mode. I have data entry below and have "frozen" the cells in which the command buttons are embedded. The problem that I am having is that when I do a print preview or a print job and return to the original view, all of my command buttons move to the left side and many are hidden under others that show up in a small portion on the frozen section of the page. Is there some way or setting that I am unaware of to "lock" my command buttons into place to prevent this from happening? I have attached file here for someone to have a look and advise me what the problem could be?

I appreciate any input.

Thanks for this forum.

Mike P

**Glaswegian** · 01-10-2006, 01:16 PM

Hi Mike and welcome to TSF.

To sort this, go into Design Mode, then right click on a button and select Format Control. In the dialog box that appears choose the Properties tab and check the option 'Move and size with cells', then click OK.

I changed all the buttons on your sample (I had to break the sheet password - you'd left it protected

) and re-attached the file.

Regards

Edit: Weird - won't let me add an attachment - sorry!

scoochie · 01-10-2006, 02:38 PM

Excellent, Thank you very much.

I am also having another problem with assinging hyperlinks to elements of a drop down list and having them show up in the list as simple desritions rather than hyperlinks. I have tried this with both the list and combo boxes from both the forms toolbar and the control toolbar with a variety of small VBA codes I have found on the web but I just cant seem to get it to work. I prefer a combo box and all I want to be able to do is have a simple first name list that can be selected from the drop down menu and is hyperlinked to start outlook with their address pre completed. I can do this and have the email address show in its entirety in the combo box, but I do not want it to display that way. Once I have this sorted, I will be able to do similar lists with Company names and websites as well as a dropdown list of shortcuts to local network folders and resourses.

If you can offer any advice on this one, I would be grateful.

**Glaswegian** · 01-10-2006, 03:09 PM

Hi Mike

I think I did a similar thing in this post

http://www.techsupportforum.com/ms-o...-list-box.html

My last post has an example workbook attached - see if it does what you need.

Regards

scoochie · 01-10-2006, 05:52 PM

Thanks Glaswegian,

I had found that exact same example in a google search no doubt posted on this forum yet I had not found this forum nor registered then. I see that it works in the spread sheet but I have had no success in copy and pasting the operations of VBA associated with it into another spreadsheet. I really am puzzled by why I can't get it to work...perhaps later I will try again and send you my worksheet.... I really kind of enjoy playing around with it and I have done many internet searches regarding it but as I am working on a spreadsheet that I would like to complete in due course the expert help is very welcome.

Cheers!

01-10-2006, 01:16 PM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,482 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi Mike and welcome to TSF. To sort this, go into Design Mode, then right click on a button and select Format Control. In the dialog box that appears choose the Properties tab and check the option 'Move and size with cells', then click OK. I changed all the buttons on your sample (I had to break the sheet password - you'd left it protected ) and re-attached the file. Regards Edit: Weird - won't let me add an attachment - sorry! __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner Last edited by Glaswegian; 01-10-2006 at 01:17 PM.

01-10-2006, 02:38 PM	#3 (permalink)
scoochie Registered User Join Date: Jan 2006 Posts: 18 OS: xp	Drop down lists and hyperlinks in excel Excellent, Thank you very much. I am also having another problem with assinging hyperlinks to elements of a drop down list and having them show up in the list as simple desritions rather than hyperlinks. I have tried this with both the list and combo boxes from both the forms toolbar and the control toolbar with a variety of small VBA codes I have found on the web but I just cant seem to get it to work. I prefer a combo box and all I want to be able to do is have a simple first name list that can be selected from the drop down menu and is hyperlinked to start outlook with their address pre completed. I can do this and have the email address show in its entirety in the combo box, but I do not want it to display that way. Once I have this sorted, I will be able to do similar lists with Company names and websites as well as a dropdown list of shortcuts to local network folders and resourses. If you can offer any advice on this one, I would be grateful.

01-10-2006, 03:09 PM	#4 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,482 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi Mike I think I did a similar thing in this post http://www.techsupportforum.com/ms-o...-list-box.html My last post has an example workbook attached - see if it does what you need. Regards __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

01-10-2006, 05:52 PM	#5 (permalink)
scoochie Registered User Join Date: Jan 2006 Posts: 18 OS: xp	Thanks Glaswegian, I had found that exact same example in a google search no doubt posted on this forum yet I had not found this forum nor registered then. I see that it works in the spread sheet but I have had no success in copy and pasting the operations of VBA associated with it into another spreadsheet. I really am puzzled by why I can't get it to work...perhaps later I will try again and send you my worksheet.... I really kind of enjoy playing around with it and I have done many internet searches regarding it but as I am working on a spreadsheet that I would like to complete in due course the expert help is very welcome. Cheers!