It's 2,500 Excel VBA Examples. AMAZING collection of 2,500 VBA examples in a question-and-answer format. Every snippet of VBA you will ever need!
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. Hi all, I've had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn't see anything, but if I am repeating prior information I do apologise. I've written a macro that is relatively simple.
It just takes some information in one format, rearranges it, adds some formatting and performs some calculations. Nothing incredibly fancy but it works fine on my computer. Now, I need to share this macro with some other people, so basically I've just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can't seem to get the macro to run properly. It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn't completed properly. I have checked Macro Security level and that is the same as mine, Tools - Add-Ins is the same, In Visual Basic, Tools - References is the same as mine.
It is the same Operating system and the same version of Excel. I have even signed into this person's computer as myself (it's a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware.
I've googled and searched and tried everything I can think of but I'm no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out! Thanks very much for your time. Thought I'd append my experience of the above problem - you can find all sorts of references to it everywhere. My problem was that a userform defined with Excel at work (containing DT pickers) gave the message in the title when opening it at home. I had a light-bulb moment and wondered whether there was a difference in the version numbers for MSCOMCT2.OCX at work and at home. Turned out the work version was newer.
I then copied the MSCOMCT2. files from work, made a backup of them at home and copied those from work to my C-drive (Windows XP - c: windows system32 ).
I then rebooted the machine - still no luck. Then, finally I unregistered the old DLL via Code: regsvr32 /u c: windows system32 MSCOMCT2.OCX (not sure if this was necessary, but I didn't think it could hurt). Reregistered the DLL via Code: regsvr32 c: windows system32 MSCOMCT2.OCX and what do you know - it worked. Summa summarum - it could be an idea to check whether the two machines have different version numbers for the MSCOMCT2.OCX files. I have AplphaNumeric part numbers that sometimes contain hyphens or decimals.
Holy Macro It's 2500 Excel Vba Examples Free
In order to make them more searchable (since people don't always use the hyphens or decimals properly when they search) I would like to create all the possible variants of the part number, but with one formula. So AHW18.787 becomes AHW18 787 and AHW18787 with =SUBSTITUTE(A1,'.' ,' ') and =SUBSTITUTE(A1,'.' ,') respectively, and AHRTW-A18-7007 becomes AHRTW A18 7007 and AHRTWA187007 with =SUBSTITUTE(A1,'-',' ') and =SUBSTITUTE(A1,'-','). What I need to be able to do is merge these formulas into one, so that no matter what format the part number is in A1, I get a version of it with spaces in B1, and a version of it with no spaces in C1. I just have not been able to nest it all together - is it even possible with this command? 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? Arcangelo from Italy asks: How can I write an Excel VBA macro to save the current Excel file with a filename derived from cell A1? This macro is amazingly straight-forward: Public Sub SaveAsA1 ThisFile = Range('A1').Value ActiveWorkbook.SaveAs Filename:=ThisFile End Sub I'm totally lost on this 'amazingly straight-forward' macro!!!!
Could someone help if I tell you the SAVE location? It's T: COMMISSIONING IJT TIMELOG project Staff#1. I'm just not certain what value I'm replacing in the Macro above. Any help appreciated. I'm using Excel 2007 and often use filters to find inconsistent data (mis-spellings, etc.) and then clean up the data using the fill handle to fill in correctly formatted values. Ui extras utorrent. I'm finding that, with a simple filter on, dragging over cells hidden by the filter changes the values in the hidden cells too.
This is something I don't remember from my last version of Excel. I'm wondering if I've mis-remembered how this control works, or if there is a setting I can't find. My goal is to drag and fill (or paste) in a colum with a range of rows wiht some hidden (filtered out) rows, and have the filtered out data be unaffected, if that makes sense.
Dear Sirs, Am in need for this solution very badly and what could be a better place than excelforum! I have an MS Excel File (2007 version) sample file attached, which has name, designation, blood group and so on. The last column is for hyperlinking photographs of individuals. In the same folder where I have saved this excel file, are lying photographs of individuals. While scanning the photographs, I have saved them serially i.e.
1,2,3 and so on. In the Excel file, in last column, I have given the respective serial numbers. In order to hyperlink one has to select that particular Cell, press Ctrl K and you automatically go to the folder containing individual photographs, you select that photo and OK.
Problem: I have to do this hyperlinking one by one and if there 1000 photos, lot of time is wasted. Solution Needed: Just in case of excel formula, which we copy and paste, Can I get a command by virtue of which the column titled Photo or column next to it gets automatically Hyperlinked to respective photo WHEN I copy and paste such command to all cells in that column. Thanks a million and warm regards::: Jack. I have been using Excel (XP) to make a text chart for several months.
Some of my text entries are rather lengthy. The past two weeks these lengthy entries are showing up as pound signs (#########) when I click off the cell. I know the text will fit in the cell, and the problem isn't solved by making the cell bigger or using a little bit less text. I have the cells formatted as 'text' and 'wrap to fit'. I have printed the pages and the printed version also has pound signs. I just want my text to show up!
I am using XL2007 and have a macro that refreshes microsoft query connections. The issue is the refreshes only happen if you step through the macro using the debugger. When you run the macro normally, everything else functions properly, but the data is not refreshed.
Any help is appreciated. Here is an excerpt of the code: Workbooks.Open Filename:='C: Profile.xls' Sheets('SELECTION').Select Range('F3').Value = SNR ' THIS PART ONLY WORKS IF YOU STEP THROUGH THE MACRO USING THE DEBUGGER.IF YOU RUN MACRO NORMALLY THE CONNECTIONS DO NOT REFRESH ActiveWorkbook.Connections('Connection').Refresh ActiveWorkbook.Connections('Connection14').Refresh ActiveWorkbook.Connections('Query from CProfile').Refresh ActiveWorkbook.Connections('Query from CProfile1').Refresh. 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. I'm trying to write a formula that will automatically take a group of football (soccer) scores, compare them with a set of predictions, and then allocate points according to how close the predictions are to the actual scores. Manchester United 3 - 0 Arsenal Prediction: 4 - 1 Points: 1 I've found a formula online that works for most scores; the correct score (e.g.
3 - 0), a correct win (e.g. 4 - 1), and in the case of a draw (e.g Man Utd 1 - 1 Arsenal, and the prediction 2 -2), correct draw (but not the correct score). The points are as follows: Correct draw (Prediction = result): 4 Correct win (Prediction = result): 3 Correct win, incorrect score (Winner correct, score not): 1 Correct draw, incorrect sco 1 The formula I found online for all but the correct draw is: =IF(A3&B3=C3&D3),3,IF(AND(A3=B3,C3=D3),1,IF(AND(A3B3,C3D3),1,IF(AND(A3.