Button On Toolbar To Autofit Both Column Width And Row Height |
Button On Toolbar To Autofit Both Column Width And Row Height - Excel |
|
Is there a way I can add a button to my toolbar so that when pushed the selection will have it's row height autofitted and the column width autofitted? I paste a lot of data from Access and don't want to have to go through the trouble of selecting all the columns, changing the width, then double clicking in between the columns and rows to get it autofitted.
I know a macro could be created, but I want to be able to use this button on any workbook I open up and without having a macro listed in every workbook I create.
I am using Excel 2003.
I know a macro could be created, but I want to be able to use this button on any workbook I open up and without having a macro listed in every workbook I create.
I am using Excel 2003.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
(80% Discount Ends Soon!)
Similar Topics
Hello,
As the title goes this is as simple as it gets. The macro worked good when I was using excel 2003 but now that I have 2007 everything works for except the resizing (Picture.Width & Picture. Height).
Code:
I have tried to record some macros while I am resizing the pictures but to no avail the macros are blank.
Please help
Thanks!
As the title goes this is as simple as it gets. The macro worked good when I was using excel 2003 but now that I have 2007 everything works for except the resizing (Picture.Width & Picture. Height).
Code:
Sub AddImage()
Dim strPath As String
Dim strFile As String
Dim Picture As Object
Dim Response As String
Dim C As Range
strPath = "C:\\Photos\"
For Each C In Range("A1", Range("A65536").End(xlUp))
If C 0 Then
With C.Offset(0, 1)
Set Picture = Nothing
On Error Resume Next
Set Picture = Sheets("Raw Pix").Pictures.Insert(strPath & C.Value & ".jpg")
Picture.Top = .Top
Picture.Left = .Left
Picture.LockAspectRatio = msoTrue
Picture.Width = 157
Picture.Height = 138
End With
End If
NextC:
Next C
End Sub
I have tried to record some macros while I am resizing the pictures but to no avail the macros are blank.
Please help
Thanks!
I'm having a problem in a workbook with several ActiveX command buttons. I had been using the form control buttons to run macros, but the boss wanted each button to have it's own, different color. So I removed the form control buttons and created new ActiveX command buttons. I got into the button properties and set the background colors. I added the _Click code to run the macros when the user clicked the buttons.
All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. Nothing.
If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name "cmdButtonGetInfo" and "=EMBED("Forms.CommandButton.1","") in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work.
It's like the buttons are being disabled when I close or open the file. Any suggestions?
All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. Nothing.
If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name "cmdButtonGetInfo" and "=EMBED("Forms.CommandButton.1","") in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work.
It's like the buttons are being disabled when I close or open the file. Any suggestions?
I have an Excel workbook that was created by a former coworker. It includes a macro that, among other things, displays a message box about the 2008 file. The macro runs as soon as the file is opened. I'd like to access that macro to correct the date to 2011 and see what else, if anything, it's doing for me (it doesn't appear to do much). I can find references to creating macros to hide and unhide rows/columns and I found ways to delete all macros in a workbook, but I cannot find anything about unhiding a macro without knowing its name.
Does anyone know of a way to unhide this macro?
Thanks!
Does anyone know of a way to unhide this macro?
Thanks!
Hello everyone!!!!
Here is my situation. I have been trying to learn macros but have not made one yet. Need your help. Here at work, I have some incompentant computer workers who claim they KNOW MS sooo well and have certificates etc... *COUGH COUGH* ya ok... LMAO
We have a "shared" work book we all work in. It's nothing fancy, just use it as a PO Book but since it is shared some people continue to not save before they add in more work so that they see a "refreshed" copy of the workbook before they start writing in cells that already contain information.
How to I create a macro button at the top of the page that is literally a SAVE button.. instead of them saving by going to File - Save or cntrl+s ??? I just thought that MAYBE having a big button in their face will remind them.
Secondly as back up I was wondering if you can create a macro to do a refresh of everyones screens automatically - say every 2 or 5 mins??? I don't mean a save... but a refresh so the screen actually updates in front of you. (although I will keep this little piece of magic out of their knowledge so they don't rely on it, just though it would be a secondary back up to helping eliminate these mishaps cause we are loosing alot of information by people saving overtop of other peoples work and not caring.)
Thanks so much!!!!! Muchly appriciated.
Here is my situation. I have been trying to learn macros but have not made one yet. Need your help. Here at work, I have some incompentant computer workers who claim they KNOW MS sooo well and have certificates etc... *COUGH COUGH* ya ok... LMAO
We have a "shared" work book we all work in. It's nothing fancy, just use it as a PO Book but since it is shared some people continue to not save before they add in more work so that they see a "refreshed" copy of the workbook before they start writing in cells that already contain information.
How to I create a macro button at the top of the page that is literally a SAVE button.. instead of them saving by going to File - Save or cntrl+s ??? I just thought that MAYBE having a big button in their face will remind them.
Secondly as back up I was wondering if you can create a macro to do a refresh of everyones screens automatically - say every 2 or 5 mins??? I don't mean a save... but a refresh so the screen actually updates in front of you. (although I will keep this little piece of magic out of their knowledge so they don't rely on it, just though it would be a secondary back up to helping eliminate these mishaps cause we are loosing alot of information by people saving overtop of other peoples work and not caring.)
Thanks so much!!!!! Muchly appriciated.
I'm trying to freeze the widths of columns so that when users of the
worksheet are using it, they can't change the width. It's for editors who
write headlines for specific areas that can only be as long as the column is
wide or the text won't fit.
worksheet are using it, they can't change the width. It's for editors who
write headlines for specific areas that can only be as long as the column is
wide or the text won't fit.
Hi,
I made a budget with many narrow columns and have turned the data into a table. I will only ever filter the data with the left-hand most column. However, all the other columns are filled each with the grey drop-down filter arrow button, and I can no longer see the headings of these columns and frankly seeing 50 little unused arrow buttons is rather ugly. Is there any way to simply remove the filter drop-down buttons on some columns or even all of them, just leaving a filter button at the heading of the first column? I know I can hide the headings, but then I lose sight of the first filter arrow that I use.
Sorry to ask such a bizarre question, but I appreciate your help.
Thanks
I made a budget with many narrow columns and have turned the data into a table. I will only ever filter the data with the left-hand most column. However, all the other columns are filled each with the grey drop-down filter arrow button, and I can no longer see the headings of these columns and frankly seeing 50 little unused arrow buttons is rather ugly. Is there any way to simply remove the filter drop-down buttons on some columns or even all of them, just leaving a filter button at the heading of the first column? I know I can hide the headings, but then I lose sight of the first filter arrow that I use.
Sorry to ask such a bizarre question, but I appreciate your help.
Thanks
hi,
I have a login form with an Exit button. I want my workbook to close as I click Exit button.
I have a login form with an Exit button. I want my workbook to close as I click Exit button.
I saw two threads in this forum that asked this question, with no good answer. I am posting this solution for anybody still struggling with this.
The question:
How can you prevent a cell's contents from overflowing into the next cell?
Of course, you can make the column wider or turn on text wrapping, but you might not want to. Each of those solutions can mess up the layout of your sheet. You may just want to truncate the value.
Some people have suggested putting a space in the next cell. This is unnecessary, a pain in the but, and will mess up any ISBLANK type formulas, among other things.
The solution:
Select the cells in question and turn on text wrapping (Format>Cells>Alignment>Wrap Text).
Now select the row(s) in question and manually set the row height, by right clicking the row number and selecting "Row Height". Check the height of an adjacent row for a good value.
Your cells will now not spill over either horizontally or vertically. They will simply truncate anything that doesn't fit.
Be careful now, because parts of your data may be hidden. This can cause its own set of problems if one or two digits are neatly hidden away. Think ahead if other people might be using this sheet, and not be expecting to have some data hidden.
Tested in Excel 2002
The question:
How can you prevent a cell's contents from overflowing into the next cell?
Of course, you can make the column wider or turn on text wrapping, but you might not want to. Each of those solutions can mess up the layout of your sheet. You may just want to truncate the value.
Some people have suggested putting a space in the next cell. This is unnecessary, a pain in the but, and will mess up any ISBLANK type formulas, among other things.
The solution:
Select the cells in question and turn on text wrapping (Format>Cells>Alignment>Wrap Text).
Now select the row(s) in question and manually set the row height, by right clicking the row number and selecting "Row Height". Check the height of an adjacent row for a good value.
Your cells will now not spill over either horizontally or vertically. They will simply truncate anything that doesn't fit.
Be careful now, because parts of your data may be hidden. This can cause its own set of problems if one or two digits are neatly hidden away. Think ahead if other people might be using this sheet, and not be expecting to have some data hidden.
Tested in Excel 2002
Hi Everybody,
First post on the forum, have only recently joined and im hoping that someone can help me before my head explodes!
I am trying to prepare a simple Material Quantity List for some Steel materials.
The materials are all in specific sizes.
What i want to do is, as i input the length, width and height of the structure in the spreadsheet, the Material Quantity List will generate the quantity of each piece of material i will need.
Does anybody know of a formula for this. I have been trying to use the IF function and getting absolutely nowhere, all suggestions will be highly appreciated!!
First post on the forum, have only recently joined and im hoping that someone can help me before my head explodes!
I am trying to prepare a simple Material Quantity List for some Steel materials.
The materials are all in specific sizes.
What i want to do is, as i input the length, width and height of the structure in the spreadsheet, the Material Quantity List will generate the quantity of each piece of material i will need.
Does anybody know of a formula for this. I have been trying to use the IF function and getting absolutely nowhere, all suggestions will be highly appreciated!!
I have an excel form, I want my users to be able to open the form, fill in appropriate data, then click the save button. When the button is clicked, the data in the form should be saved to a new file and then the original form should be reset (clear all values) for a new set of data entry. When the data is saved, I want the new excel sheet to be saved to a location specified within the macro so the user doesn't have any options and I want the title of the new file to be the value of one of the cells.
I'm new to macros and would appreciate any help this board can offer. Thanks...
I'm new to macros and would appreciate any help this board can offer. Thanks...
Posted this on the Ozgrid forums, but haven't gotten any help yet, so I thought I'd try here too!
I've been having a strange problem lately. I have a fairly lengthy macro that works perfectly most of the time. Occasionally it will run as expected but as soon as the macro ends, excel becomes unresponsive to mouse-clicks. When I click anywhere (trying to select a cell, or an excel menu item...clicking anywhere in excel) I'll get the a 'ding' system sound and nothing will happen. BUT, if I use the keyboard arrow keys, I can see that the active cell selection moves accordingly. Then it gets really strange - when I have a cell highlighted and press any key to input text, it gets duplicated. So if I press "s' it will input "ss" into the cell, and then when I press enter it will auto-move to the next cell down, but nothing ends up getting saved into the previous cell.
I don't understand what's going on at all. I can't think of anything in my macro that would have these kinds of effects. I've made sure that screenupdating is turned back on at the end of every procedure. If I go into the VBE, I can manually run procedures and they all work fine. The only way I've been able to get back to normal is by force closing excel altogether and re-opening. Any ideas?
______________________
Still having trouble, and the same thing is happening with this workbook on two different computers, so I don't think it's a hardware, or OS specific issue. When it gets locked up like this, I can still do anything in the VBE (edits cells, run macros, etc.) with no problems. If I'm in the excel window, I can click alt on the keyboard and the shortcut keys for the menu come up, but I can't go deeper than that by clicking the letter shortcuts, they do nothing. If I use the delete key to delete the contents of a cell, then it gets deleted. But if I type anything else (numbers, letters, or symbols) then it types 2 instances of the key every time, yet when I hit enter, nothing changes in the cell. I also can't really bring focus to the excel window if something else (ie. the VBE) is on top of it, clicking into the excel window just gives the little system 'ding' sound and nothing happens.
If I hit the save button in the VBE (since I can't click anything in the excel window), then it seems to snap out of it and go back to normal. I tried searching for anything simmilar to this and can't find anything...
Any help would be much appreciated. This is driving me nuts!
I've been having a strange problem lately. I have a fairly lengthy macro that works perfectly most of the time. Occasionally it will run as expected but as soon as the macro ends, excel becomes unresponsive to mouse-clicks. When I click anywhere (trying to select a cell, or an excel menu item...clicking anywhere in excel) I'll get the a 'ding' system sound and nothing will happen. BUT, if I use the keyboard arrow keys, I can see that the active cell selection moves accordingly. Then it gets really strange - when I have a cell highlighted and press any key to input text, it gets duplicated. So if I press "s' it will input "ss" into the cell, and then when I press enter it will auto-move to the next cell down, but nothing ends up getting saved into the previous cell.
I don't understand what's going on at all. I can't think of anything in my macro that would have these kinds of effects. I've made sure that screenupdating is turned back on at the end of every procedure. If I go into the VBE, I can manually run procedures and they all work fine. The only way I've been able to get back to normal is by force closing excel altogether and re-opening. Any ideas?
______________________
Still having trouble, and the same thing is happening with this workbook on two different computers, so I don't think it's a hardware, or OS specific issue. When it gets locked up like this, I can still do anything in the VBE (edits cells, run macros, etc.) with no problems. If I'm in the excel window, I can click alt on the keyboard and the shortcut keys for the menu come up, but I can't go deeper than that by clicking the letter shortcuts, they do nothing. If I use the delete key to delete the contents of a cell, then it gets deleted. But if I type anything else (numbers, letters, or symbols) then it types 2 instances of the key every time, yet when I hit enter, nothing changes in the cell. I also can't really bring focus to the excel window if something else (ie. the VBE) is on top of it, clicking into the excel window just gives the little system 'ding' sound and nothing happens.
If I hit the save button in the VBE (since I can't click anything in the excel window), then it seems to snap out of it and go back to normal. I tried searching for anything simmilar to this and can't find anything...
Any help would be much appreciated. This is driving me nuts!
If I am using sendmail in a simple macro, is there any code I can add to actually open Outlook?
Some users of the macro are pressing the macro button and do not have Outlook open so the email isn't sent until they eventually open Outlook!
Thanks in advance
Some users of the macro are pressing the macro button and do not have Outlook open so the email isn't sent until they eventually open Outlook!
Thanks in advance
Hi All,
I got the below macro which uses IE and open the URLs. I want to create a userform with listbox with radio button and commandbutton on the same which will help me to connect to each url when I select the same in listbox and click on the commandbutton.
Code:
Any suggestions..
I got the below macro which uses IE and open the URLs. I want to create a userform with listbox with radio button and commandbutton on the same which will help me to connect to each url when I select the same in listbox and click on the commandbutton.
Code:
Sub DoBrowse1()
Dim ie As Object
Set ie = CreateObject("Internetexplorer.Application")
ie.Visible = True
ie.Navigate "www.google.com"
End Sub
Any suggestions..
I have a excel spreedsheet that contain external data. I would like to put a
button in the excel sheet to update the sheet without doing right click and
refresh.
My user here are very dummies.
button in the excel sheet to update the sheet without doing right click and
refresh.
My user here are very dummies.
I have never really used VBA and so am completely stuck at this problem. I need to create a macro which auto-populates a master worksheet from the individual user sheets in a shared workbook.
Sheet 1 is the master sheet "Team Stats". There will be an undetermined number of individual worksheets to accomodate new staff.
Each worksheet will be identical, using columns A-I with row 1 having the headings:
Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell).
There will be a varying number of rows in each of the individual sheets.
If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened.
If anyone can help it would really cut down the time I spend collating these stats every day!
Sheet 1 is the master sheet "Team Stats". There will be an undetermined number of individual worksheets to accomodate new staff.
Each worksheet will be identical, using columns A-I with row 1 having the headings:
Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell).
There will be a varying number of rows in each of the individual sheets.
If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened.
If anyone can help it would really cut down the time I spend collating these stats every day!
I am looking for the code to select the visible data after applying a data filter. Actually I know how to select the data after applying the data filter but the issue is I am not able to exclude the header row and give the target range as used (non-blank) rows only!!
I am using below code to Select the Visible rows in the target range:
Code:
Problems in this code a
1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range.
2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.
E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6
3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection.
Some one please revert with the solution.
Thanks in advance.
I am using below code to Select the Visible rows in the target range:
Code:
Range("A:p").SpecialCells(xlCellTypeVisible).Select
Problems in this code a
1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range.
2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.
E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6
3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection.
Some one please revert with the solution.
Thanks in advance.
Hi
In excel i have a sheet open with about 30 columns in it. I try to use the scroll bar to move to the right and nothing happens apart from the scroll bar moves along. If i use the right button on the keyboard the scroll bar moves but i cannot see the columns I want. I cannot see anything that would of caused this.
Can anyone help?
Thanks
In excel i have a sheet open with about 30 columns in it. I try to use the scroll bar to move to the right and nothing happens apart from the scroll bar moves along. If i use the right button on the keyboard the scroll bar moves but i cannot see the columns I want. I cannot see anything that would of caused this.
Can anyone help?
Thanks
Hi all, the title pretty much says it all, I have a form where I am trying to create a button that will pop up a window where the user will browse to the desired folder and once that folder is selected, the path will be copied as a text string into the cell adjacent. My command Button cell is in B3 and the path should be shown in C3.
Can anyone help?
Thanks
Can anyone help?
Thanks
Hi there, longtime user firsttime poster. Looking for some help as I am a non-expert with macros. Here's what I'm trying to do:
We have to submit things to a certain regulatory body and we usually enter tasks in as soon as they come, do the submission, and then keep a record of that submission.
So, I have a workbook with two sheets, one is "TO DO", the other is "ARCHIVE". Both sheets have the same columns and everything. I am looking for a macro that will automatically cut a (row) from the TO DO sheet and paste it in into the ARCHIVE sheet once it is done, then delete the cut row from the TO DO list so it stays topped up.
The trigger for archiving is the columns M and N which are titled "Complete ?" and each has a validation drop down that says "YES". When both cells in columns M and N have the YES in them, I would like the macro to make the above mentioned actions.
I ran a search on the forums and found something similar, but not quite what I was looking for.
Any help?
We have to submit things to a certain regulatory body and we usually enter tasks in as soon as they come, do the submission, and then keep a record of that submission.
So, I have a workbook with two sheets, one is "TO DO", the other is "ARCHIVE". Both sheets have the same columns and everything. I am looking for a macro that will automatically cut a (row) from the TO DO sheet and paste it in into the ARCHIVE sheet once it is done, then delete the cut row from the TO DO list so it stays topped up.
The trigger for archiving is the columns M and N which are titled "Complete ?" and each has a validation drop down that says "YES". When both cells in columns M and N have the YES in them, I would like the macro to make the above mentioned actions.
I ran a search on the forums and found something similar, but not quite what I was looking for.
Any help?
I've lost my menu bar in Excel. I tried to enable Worksheet Menu bar, but
that is not even listed in my toolbar section in the Customize box. Any
other ideas?
that is not even listed in my toolbar section in the Customize box. Any
other ideas?