alanexcel.dk


File No Categori Sub categori Explanation Excl  VBA Code


36 User General The user can input/change/erase data in a database "Databasen" which is placed in an excel-sheet by clicking on a button in the sheet "Demo". The input is done through an Userform No


33 User Assistance Through the VBA-code a userfunction has been made "=checktal(xxx)". This function makes it possible to convert normal numbers into numbers spelled out in words, like used on cheques. The code contains the words in danish - but it is easy for you to edit t No


54 User Assistance Shows listboxes used between two files, file 1 has the basic data and file 2 is using those Yes


2 User Assistance In this file the user do not have to input the ":"-sign in timedata like 09:00 AM No


56 User Assistance In this file the useren do not have to input the "  -  -  "-signs in dates No


32 User Assistance Simular to file no 33 but converts normal numbers to romam numbers like "XXVI". No


11 User Assistance Shows a chart with projekt titles, startdates, duration and enddate. Yes


27 User Assistance A userfunction is made - and with matrixformlulars - that automatically splits the difference between two dates into number of days, months, years No


49 User Assistance Shows the actual dates and running time in a sheet No


38 User Kontrolling By a button in the "Sheet1" 3 examples of controlling the userinput is demonstrated; number of characters in a cell, a number must be a fixed min. and that a number can be transformed into a date No


37 User Kontrolling Excel's own validation is demonstrated in this file Yes


26 User Orientation for Many times the user must belive in that the macro really is doing something.
Here is some examples on how to inform the user about how things are going along No


28 User Orientation for Many times the user must belive in that the macro really is doing something.
Here is some examples on how to inform the user about how things are going along No


29 User Orientation for Many times the user must belive in that the macro really is doing something.
Here is some examples on how to inform the user about how things are going along No


21 File_Cell_Range General This example erases all empty rows in a sheet No


39 File_Cell_Range Cell Example in Excel's own datavalidation Yes


5 File_Cell_Range Cell Different ways in showing the user if the value of a cell is negative No


6 File_Cell_Range Cell Different ways in showing the user if the value of a cell is negative No


7 File_Cell_Range Cell Different ways in showing the user if the value of a cell is negative No


8 File_Cell_Range Cell Different ways in showing the user if the value of a cell is negative No


16 File_Cell_Range Cell Userfunctions that returns the values in the last row or col No


17 File_Cell_Range Cell Userfunctions that returns the values in the last row or col No


43 File_Cell_Range Cell Moving the "-"-sign from the end of a exported number to the start so that the numbers can be used in Excel No


66 File_Cell_Range Cell Control wether a given cell is marked No


68 File_Cell_Range Cell Runs through the file and finds a certain cell value No


69 File_Cell_Range Cell Sorts and find the highest date in a col No


70 File_Cell_Range Cell Finds the address on the active cell No


72 File_Cell_Range Cell Change format on numbers to text No


74 File_Cell_Range Cell Erases alle cells with a certain value No


75 File_Cell_Range Cell Erases cells with the same value No


76 File_Cell_Range Cell Sorts a col according to the presence of the datanumber No


25 File_Cell_Range File Sorts all sheets in alphabetical order No


9 File_Cell_Range File Both files find *.xls files in directories but in two ways One only in the present directory, the other in a directory according to what the user has put into the code. The files will be listed in col. "A" in "sheet1". The directory when you download the  No


10 File_Cell_Range File Both files find *.xls files in directories but in two ways One only in the present directory, the other in a directory according to what the user has put into the code. The files will be listed in col. "A" in "sheet1". The directory when you download the  No


44 File_Cell_Range File In the first file the files itself will be saved without alerting the user. In the second file the file can be saved with a specified name including the date No


45 File_Cell_Range File In the first file the files itself will be saved without alerting the user. In the second file the file can be saved with a specified name including the date No


42 File_Cell_Range File Here you have a sheet with 10 col and 10 rows that is put into TXT-format in a file No


63 File_Cell_Range File Here it is possible to get the sheets named according to a list in a sheet No

1 File_Cell_Range Range If you have a col with a lot of data in it, it may be difficult to read. This example colors every second row grey. No


3 Chart   A "living" chart line No


4 File_Cell_Range Range Exampel where you through VBA finds the range for print No


12 Toy   A little bit of fun. Here you get figures drawn in a sheet to rotate when you click them No


13 General   Handy little tool that makes a list of all install fonts No


14 Toy   Gives the opportunity to give the user a verbal help or ... No


15 Toy   Through the code Excel will play a little wav-file. Remember to download the wav-file too No


64 File_Cell_Range File The name of the user may be put into the line in the active window, top left No


71 File_Cell_Range File The user may move a file from one dir to another as mentioned in the code No


73 File_Cell_Range File Every second row is marked in Bold No


30 Pivot   The result of a pivottable is being used as base for a chart Yes


18 File_Cell_Range Range 3 different ways of refering to a range, please choose the one that fits you the best No


19 File_Cell_Range Range 3 different ways of refering to a range, please choose the one that fits you the best No


20 File_Cell_Range Range 3 different ways of refering to a range, please choose the one that fits you the best No


34 General   From time to time it may be difficult to limit one self, so it is nice if you can find out which sheets/files the present file is

liked to. In this example you may erase the link etc or not No


35 Toy   If you ever should want to see what colors a picture in col "A" is made out of, then you cannot live without this No


22 Chart   This one makes it easy to add a new col.'s data to a chart No


23 General   2 examples of importing a textfile. The first imports without user doing anything and the second allows the user to decide

what to do. No


24 General   2 examples of importing a textfile. The first imports without user doing anything and the second allows the user to decide

what to do. No


53 General   Copies filtered data either to a new sheet or file No


40 General   The first example makes it possible to change the date format from ÅÅ-MM-DD to DD-MM-ÅÅÅÅ. The second example does the same, but the user may change the format No


41 General   The first example makes it possible to change the date format from ÅÅ-MM-DD to DD-MM-ÅÅÅÅ. The second example does the same, but the user may change the format No


55 General   Try your luck with this if you are gambling in the Lotto - no guarantee for a winn ofcourse No


52 General   If you refund travel (driving) a lot then this example might be of use for you, it makes it easy to pay for the same number of kilometers, driving distances etc Yes


50 General   Finds a certain number of the largest numbers in a col Yes


51 General   Sums up a col of numbers according to criterias Yes


57 General   After clicking the button the file will close after 10 sec No


48 General   Get the formular in cell A10 shown in cell B10 by this code No


46 General   This litte one adds some very used icons to my toolbar No


47 General   Remove dublets (the same number mentioned two or more times) from a row so that only one item of the number is left in the sheet.
Remove dublets (the same number mentioned two or more times) from a row so that only one item of the number is left in the s No


58 General   Prints automatically all the unique sets of data in a autofiltered range No


59 General   If you f.ex. have customerno. in col. A you can get the same customer number colored in the same color in this example No


60 General   All red, black and blue numbers are added together in totals for the red, blue and black ones No


61 General   If you input the starting date of a month a day plan is automatically made Yes


62 General   Converts alle formulars and links to values No


65 General   Control that the directory mentioned in the code is on the harddisk No


67 General   Controls wether the sheet mentioned in the code is in the file No


31 Pivot   Example in consolidation of three product done by a pivottable Yes


77 File_Cell_Range File New sheets gets numbered in row No


78 General   Makes an autofilter and prints results one after one No


79 File_Cell_Range Cell Changes the value between two cells No


80 General   Controls if a certain dir exist No


81 File_Cell_Range Range Informs the user which col that is active No


82 General   Finds the time it takes for the macro No


83 File_Cell_Range Range Finds and renames ranges in a sheet No


84 File_Cell_Range Cell Kombines the value of cells No


85 File_Cell_Range Cell Finds and erases data No


86 File_Cell_Range File Generate random numbers without repeating one No


87 File_Cell_Range File Goes from sheet to sheet in the file No


88 File_Cell_Range File Makes all combinations from agiven list in a col No


89 File_Cell_Range Cell Remembers the cell that you came from No


90 General   Get the autocorrecturlist into a sheet No


91 General   List's all files from a given dir into a sheet No


92 File_Cell_Range File Input data in all sheets at once No


93 File_Cell_Range File Controls if the sheets-named in a list in a sheet is in the file No


94 File_Cell_Range File Controls if it is a nomal sheet or an chartsheet No


95 File_Cell_Range File Copies the present sheet x-number of times No


96 File_Cell_Range Range Copies the visible cells into an other sheet No


97 General   Copies data mentioned twice into an other sheet No


98 General   Copies data from one sheet into an other according to criteria No


99 User Orientation for Informs the user what sheet(s) that is active No


100 File_Cell_Range Range Collects data in col A and B an erases the original data No


101 General   Changes the place of caracters No


102 General   Controls if the file exist in the present dir No


103 General   Autofilters and copies to sheet and name theese No


104 User General Search according to the user input and mark it No


105 File_Cell_Range File Search for a specific file in a specific dir No


106 File_Cell_Range Cell Search numbers in sheet1 and put references in sheet2 No


107 File_Cell_Range Cell Remove formulars with error-values No


108 File_Cell_Range Cell Erases repeated values in cells below No


109 File_Cell_Range Cell Insert text in more than one line in a single cell No


139 General   Hides the present file and activates an other open file No


140 General   Autofilter according to criteria in the code and place in new sheet(s) accordingly No


141 General   Sheetprotection on/off with password No


142 File_Cell_Range Cell Use a cells value as criteria for autofilter No


143 General   Mark values mentioned twice with color and copy them to sheet2 No


144 File_Cell_Range Range Changes cells with #ERRORS in them to 0 No


145 File_Cell_Range Range Filtered data is copied to new sheet that gets the name of the criteria No


146 File_Cell_Range Cell Search after the column header mentioned in the code and place the cursor in that column No


147 File_Cell_Range Range Finds values mentioned twice in col. A and erases for 4 col No


148 File_Cell_Range Range Copies data from sheet1 to sheet2 and inserts a blank row No


149 General   Goes to the sheet before this No


150 General   Inserts a new row UNDER the active cell No


151 General   Copies a range from the active file to a new file reserving a blank line No


110 General   Go from sheet to sheet through a macro No


111 General   Saves  a sheet from the present file under today's date No


112 General   Saves a single sheet in a file according to the user No


113 General   Insert an autofilter in more than one sheet No


114 General   Inputs data i specific rows No


115 File_Cell_Range Range Compares col A and B, tranfers data mentioned twice to col C No


116 User Assistance Makes the user aware that a sort must happend No


117 File_Cell_Range File Copies between dir's, checks if the file allready is present No


118 File_Cell_Range Range Copies a choosen part to a new file No


119 File_Cell_Range Range Copies a specific number of data between sheets No


120 File_Cell_Range Range Copies specific data to a new file as last sheet No


121 File_Cell_Range Range Copies the value of celle A1 into more cells No


122 General   Copies data incl the formats of row and column No


123 General   Makes small lines of text No


124 General   See the excel colors and ref.no No


125 General   Close all files and if you want, Excel itself No


126 General   Close the file with or without SAVE No


127 General   Close the file and erase it from the harddisk No


128 General   Change the filedate according to the user No


129 File_Cell_Range Cell Mark the cell with the highest value, see file no. 131 No


130 File_Cell_Range Range Mark cells to the right of the active cell No


131 File_Cell_Range Cell Mark the cell with the maximum value, see 129 No


132 File_Cell_Range Range Marks a certain number of rows according to the user No


133 General   Moves the names for ranges into the comment field No


134 General   Splits a text into each caracter in each new cell No


135 File_Cell_Range Cell Hides the cells marked in red in a col No


136 General   Sum's rows according to the user No


137 General   Random numbers/values No


138 File_Cell_Range Range Specify, in col. A,  the highest value in a range No


152 File_Cell_Range Range Combines sheet1 and sheet2 to sheet3 without repeating data No


153 File_Cell_Range Range Transfer the value of special choosen cells No


154 File_Cell_Range Cell Copies a formular according to the number of rows in the column to the left No


155 File_Cell_Range Range Erases/copies data with the letter "a" No


156 File_Cell_Range Range Copies a range to an other range directly mentioned in the code No


157 File_Cell_Range Range Copies every second row, starting with the row decided by the user No


158 File_Cell_Range Range Copies according to 2 criteria, number and text No


159 General   State a dir and get the dir's under that No


160 User Assistance Insert 12 sheets (the month's) and limit the kind of userinput to them No


161 General   Copies ONLY the number out of celle without f.ex. the color of them No


162 General   Copies if the column A has a numeric value No


163 File_Cell_Range   Automatic transfer of data to another sheet No


164 General   Makes it possible to state the height of row in cm No


165 File_Cell_Range Range Search for a special cell value and put in sheet2 with a reference No


166 General   Given a firstletter = a specific number No


167 General   Makes it possible to add items to the autocorrecturlist in Excel No


Please send an e-mail to alan(@)alanexcel.dk if you have questions/remarks about this site.