|
The 2500 VBA Examples CD is an amazing resource. A joint project between Germany's Hans W. Herber, Tom Urtis, and MrExcel, the CD is the most comprehensive reference on Excel VBA available today. Check out the solutions available on the CD.
If installing in Vista, read this note.
Click Here to Order!
Click Here to Order!
API-Calls
Back to top
Click Here to Order!
Arrays and Array Functions
- Temporary use of VBA in a matrix formula - The values from column C are to be summarized by criteria with the temporary use of a matrix formula
- Total up numbers with considering of month and year - Entries for the respective month in area A1:A100 are to be counted with consideration of the year
- Calculate values from a measuring table - According to criteria in cells G2:G3, the total of column D is to be calculated.
- Read files with user defined matrix functions - Values form area A2:B4, if they are equal or higher than 5, are to be exported with a matrix formula
- Count of values with various conditions - The number of matching data in table A4:C405 with criteria in area A2:C2 shall be read.
- Total up hours and minutes separate - Total up the first and last cells in the sports results from column B
- Sums with consideration the upper and under limit - How can I check Column A with Values with set upper and lower limits and add up the Cells right of it?
- Redimension of an Array through several Dimensions - How can I avoid an Array Re-dimension only being permitted with the exclusive last Data-Area Dimension is changed?
- Ascertain the number of matches in two Areas - How can I ascertain the Values that appear on the same position in two Areas?
- Ascertain number of 2 criterias with the D-Function and MatrixForm. - How can I ascertain the number of RecordSets using two Criterias?
- Ascertain the number of the Weekdays in a set period - How can I ascertain the number of Weekdays in a set period?
- Search for a Value in an Array without query each DataField - How can I search for a Value in an Array without asking each DataField?
- Handling Arrays in userdefined Functions - How can I handle Matrixes in userdefined Functions? How are individual Values returned?
- Fit in the FolderName of a Directory in a Cell - 736/99 Subject:Fit in the FolderName of a Directory in a Cell Question Date:12.06.99 Topic:Matrix Version:Excel5/7, Excel8 question:How can I ascertain the FolderNames of a cell written DirectoryName via an userdefined Function?
- Take the Values of a CellField in a Matrix and read it out - How can I read in a CellField of a Worksheet in a Matrix and read out the separate CellContents afterwards?
- Sort Boxes in an multidimensional matrix - How can I sort the elements o a multidimensional array in ascending order?
- Select Values according to two Criterions - How can I sum the Values according to two Criterias? Countif and Sumif only allow one.
- Calculate a Matrix new - How is a Matrix recalculated after adding or deleting of Numbers?
- Output of an userdefined Function in a MatrixFormula - How can I make a userdefined Function give out several results?
- List the DistanceMatrix - How can I translate a DistanceMatrix into a List?
- Sum the Values of a certain month - How can I determine the Sum of the Values of a certain month when I have entered the days of a year in the Date Format in Column A and a Value for each day in column B?
- Total of Products - I have a Worksheet with the Columns "Article", "Price" and "Quantity". How do I calculate the Total of the Products of Price and Quantities for specific Articles?
- Divide a multidimensional matrix into separate linear arrays. - Divide a multidimensional matrix of 12 months in serial number format into two separate linear arrays.
- Entry of array formulas in a month and year matrix. - Macro to enter array formulas that sum values in a matrix of dates.
- Array formula to return quantity of days - The array formula in cell A1 returns the number of January days in 2002 that are listed in column A of Sheet2.
- Add numbers on each side of a character. - Add numbers to the left and right of the colon character.
- Array and non-array formulas - Two formula approaches (array and non-array) to count the quantity and sum of values that fall between the numeric maximum and minimum ranges in cells C1 and C2 of Sheet1.
- Determine a value based on two criteria - A value is returned from a table based on two criteria.
- Count the match of dual criteria. - In cell A1, the array formula returns the number of times a dual criteria match is met.
- Array formula sums values within starting and ending dates - The array formula on Sheet1 uses a start date and end date (each as a named range) from the table on Sheet2 to sum the values in column B whose dates in column A fall within those start and end date ranges.
- Populate and split an array - A linear array is to be filled with the numbers from 1 to 100, and be divided into a multidimensional range of 4 columns and 25 rows.
- Conditional format and array formula - The cells in the table range A3:AW15 are conditionally formatted to be shaded yellow when their values match the criteria value range in E19:J19.
- Array formula to match and sum values in respective columns - Use an array formula in column J that involves the occurrence of values in columns D, F, G, and I that match the value of each record in the cell of its respective column.
- Values summed in row at intervals - Each cell in row 1 contains numbers from 1 to 256 that are summed at intervals set in a criteria cell.
- Costs by month, department, and purpose are calculated and cross-checked - Costs by month, department, and purpose are calculated and cross-checked
- Summary distribution by month - How can I summarize daily activity for 5 employees by month using array formulas, not VBA?
- Alert for non-specified value in range - What formula can I employ to alert me when a non-specified value exists in a range?
- Count days in each month between start and end dates - How can I compose a set of 12 array formulas that return the number of days for months that fall between a start date and an end date?
- Multiply a sum of elements - How can I sum each numeric element in an array, and multiply that product by each element?
- Average numeric array elements based on two criteria - I have a 3-column array. How can I return the average of numbers in the third column whose elements in column 1 and column 2 match two specified criteria?
- Sum and count array elements with two criteria - I have a two-column array. How can I sum the numeric values in column 2 and count how many numeric elements I am summing, based on a Ceiling and Floor criteria for corresponding elements in column 1?
- Comparison of sets of elements - I have a four-column array. How can I determine how many elements' values agree between column 1 and column 3 compared to column 2 and column 4, and between column 1 and column 2 compared to column 3 and column 4?
- Sum array elements based on two criteria - I have a two-column array, where column 1 is a list of dates and column 2 is numbers. How can I sum the numbers in column 2 whose date in column 1 is of a specified year and month?
- Minimum array element value - How can I determine which numeric element in column 2 of a two-column array is the minimum value among the other elements like it whose corresponding value in column 1 matches a pre-set criteria?
- Minimum quotient from multiple column array - I have a multiple column array, where the numeric elements in row 2 are divided by the numeric elements in row 3. What formula can identify which non-error quotient is the smallest (minimum) value?
- Count empty and occupied array elements - I have a two-column array, with some cells occupied and some cells empty. How can I count how many records in the array are occupied with both elements, and how many where both elements are empty?
- Sum based on date criteria - I have a two-column array, with dates in column 1 and numbers in column 2. How can I sum only those numbers in column 2 whose dates are less than or equal to six months prior to the current date?
- Row and address of found element in array - I have a single column array with unique values. How can I return the row and address of a given value?
- Average of numeric array elements based on criteria - I have a two-column array, with recurring values in column 1 and numeric values in column 2. How can I average the numeric values whose element in column 1 matches a given criteria?
- Last row with data - For a given range on my worksheet, how can I determine what the last row is that displays data?
- Count and sum array elements based on two criteria - I have a three-column array. How can I sum and count the numeric elements in column 3 based on two criteria that need to be met - - one for column 1 and one for column 2?
- Count elements between Floor and Ceiling criteria - How can I count the elements in an array that fall between and including Floor and Ceiling set of criteria?
- Sum numeric array elements based on character string criteria - I have a two-column array, with column 1 containing text and column 2 containing numbers. How can I sum the numbers where the column 1 text begins with a two-character criteria I specify?
- Count weekdays between dates - What formula can tell me how many times a given weekday will fall in between two dates?
- Count frequency of character in a string - How can I count the number of times a given character exists in a string?
- Average numeric array elements between Floor and Ceiling criteria - I have a single column array of numbers. How can I identify and average the numbers that fall within and including a specified set of Floor and Ceiling criteria?
Back to top
Click Here to Order!
Built-in Functions
- Capital gain with fixed and variable rates - Capital gain with fixed and variable rates
- Determine travel times from a travel matrix - How can I determine the valid departure time with entering two criteria in a travel matrix
- Insert values depending on worksheet name - Data from the sheet named in cell B2 is to be inserted in area A1:D8
- Increase position number for list entry - The position number in column B is to be increased by 1 after a new entry in column C
- Collection of job descriptions by employee - The number of job descriptions per employee is to be listed in sheet 2
- Determine values depending on selected validation - Cell H10 contains a validity limitation. Corresponding values from selection in H10 are to be inserted in cell left to H10.
- Multiply values depending on assigned indexes - A value is assigned to every letter in column A. This assignment is in sheet 2. According to this assignment, the values are to be multiplied by 3.14
- EURO currency increments - The value in cell A3 is to be divided up in coins and bills of the EURO currency
- Total without maximum value, mark maximum value - Values in column F are to be totaled up in the respective rows without consideration of the maximum value. The respective maximum value is to be marked
- Determine row numbers of found cells - Found row numbers from column A with values from column B are to be entered in column C.
- List of weeks for various years - The calendar weeks from/to of months are to be listed. The year in cell B1 is to be kept variable. No use of VBA
- Use of DBMAX for date and time - In sheet2, the maximum temperature of a given day and the time is to be calculated.
- Calculate average from 6 highest values in column A - How can I calculate the average value from the 6 highest values in column A?
- Integration of SREFERENCE in a names function - Value in column A is to be searched in sheet 1 or sheet 2. If respective cell in column B is not empty, recognize cell and link it with cell from column A.
- Values with limited lifetime - Formula in cell E2 should only go up to 10/31/2001. Test with change of system time. Then format formula with F2
- Search of respective values in two columns - Data from column C is to be searched in column A and B and the value is to be displayed in column D
- Indicate data in matrix. - By calendar weeks listed values in Sheet2 should be listed in Sheet1
- Number of entries for a specific week - The number of entries in column a for a specific week is to be determined.
- Multiplication of a value depending on its position - In area A1:E1 there is only one value. How can I multiply this value with a factor depending on the column position?
- Ascertain the sum between two periods - How can I ascertain the turnover being in Column B of a certain period? The start day is in Cell C2, the day of end in Cell D2 and the row of date is in Column A.
- Round-Examples - How must I handle the Round-Functions?
- Show the number of the determined RecordSets via Autofilter - How can I read out the RecordSets that were determined by the Autofilter? The same Message that appears with the use of the Autofilter in the StatusBar should be outputted.
- Search through a two-dimension Matrix with the VLOOKUP-Function - How can I search for a Value in a two-dimension Matrix that isnīt entered in a Worksheet without checking each Value?
- Ascertain the next free article number - How can I ascertain the next free article number? Spaces must be taken into account. The article numbers are in the Format "10000ABC".
- Calculation of the sales tax from gross and add the sales tax - How can I calculate the net fare and sales tax of the gross amounts in a worksheet? The sales tax should be added depending on the sales tax rate.
- Access on Cells by using an Area Name - How can I access Cells of a second Worksheet by using an Area Name and the INDIRECT-Function and transpose the Area with it?
- Ascertain the confirmation of an invitation - How can I ascertain, how many invitations have or have not been answered and how many confirmations with how many persons, exist till now?
- Find double Values via a Formula and mark it - How can I create a comment to the double existing Values of Column A in Column B and mark these in Column A?
- Overview-Sheet with information about the names in the Worksheets - Iīve got 2 Worksheets with a Column for name and surname each time. How can I view a X with the name as well as the surname in one Row below the WorksheetName in an Overview-Sheet
- Formula Input via ShortCut - How can I multiply the Value of Column A with the Value of Column B and format the Cell when I press the ShortCut?
- Read out names depending on a score - There are names in Column A and scores in Column B. The lowest score means the first place. How can I list the names according to there order of rank in another Column?
- Enter Address according to the selection in the ValidityField - How can I enter the address in addressCells after selecting a customer number in a ValidityField?
- Calculate a mark on the basis of a PointTable - How can I ascertain a mark for a given score on the basis of a PointTable?
- Calculation of period and mean value - How can I calculate within a given period a mean value per hour? The Endtime can be after midnight.
- Show a pre-determined Number of the lowest Values - How can I view the lowest value of column A and B in Cell C3?
- Ascertain the value depending on a Symbol in a String - How can I change the value of of a cell to be "Man" or "Woman" depending on the 6th Cell value of 1 or 2?
- VLOOKUP, HLOOKUP, INDEX, MATCH and Goal Seek - How do the Functions VLOOKUP, HLOOKUP, INDEX, MATCH as well as the Goal Seek, work ?
- Variations to view a weekday? - In what ways can I view the Weekday of a date?
- Example for the use of the SUMIF-FORMULA - How do I handle the SUMIF-Formula?
- VLOOKUP-, HLOOKUP- und Matrixformula-Examples - How do I handle VLOOKUP-, HLOOKUP- and Matrixformulas?
- Show "done" after a RowInput - When there is an input in a respective Row, how can I make Column D display done?
- Net workdays and flexible holidays - How can I ascertain the net workdays of a period considering the flexible holidays?
- Medal-distribution according to points - How can I set the Medal-distribution at School sport Events?
- Mean Value of a Row of Numbers till the Row with the Formula - How can I ascertain the mean value of Row of Numbers till the InputCell?
- Evaluate Soccer Scores - How can I evaluate a Worksheet with Soccer scores. The agreement is as follows: Goals for = 3 Points, Goals wrong = 1 against
- Election-Analysis with Hare-Niemeyer - How can I integrate the VoteCount with Hare-Niemeyer in an Excel-Worksheet?
- Wages calculation including additional wages - How do I create a Worksheet that does an automatic Wages calculation considering additional wages?
- Multiply Header-Values of Cells that are marked with a cross - How can I multiply Header-Values of Cells that are marked with a cross with a value in the same row?
- Interest-Worksheet with variable repayments - How do I create a Worksheet that calculates the outstanding sum with a fixed interest rate and variable repayments?
- DM/EURO-conversion without Formula and VBA-Function - how can I always display a DM/EURO and EURO/DM Converter in the current Worksheet without depositing a userdefined Function or using a set Formula?
- Construction of a weight-, range zone- and price-Table - How do I construct a Worksheet with weight-, range zone-, and price-givings so that the transport cost belonging to it is shown automatically when the weight
- Handling of the Database-Function DBSUM() - How can I work with the Database-Function DBSUM() to use several criterias as a basis for the adding up of a Database Area?
- Sort rising and descending without the SortFunction - There is a Worksheet with rising customer numbers. How is the Column with the customers sorted once rising and once descending without using the SortFunction?
- Use the last Value of a sequence of numbers as multiplier at a time - How can I use the last Value of a sequence of numbers of a Worksheet as multiplier?
- Read out the h-mean value of a series of measurements that last for 10 minutes - How are the current hourly mean values of a series of measurements that last for 10 minutes read out?
- Sum the worksheet according to several criteria. - How can I sum a range based on several criteria? Excel's SUMIF function accepts only one criterion.
- Delete all names in the active Workbook - How can I delete all names in the active Workbook?
- Create the Email-Address with Name, Surname and Domain - How can I create Email-Addresses via a Formula when the Name and the Surname are in Column A?
- Debt balnce - How can I create a Worksheet that makes a monthly calculation of the outstanding debt balance with a fixed interest rate and fixed monthly pay backs?
- Determine matches from Preliminary to Final round via formulas. - How can I determine matches from the preliminary round to the final round, using formulas?
- Fit the List of the days of a month exactly - How can I fit the date of a List of month days exactly to the actual days considering the leap years?
- The last Cell with contents of a Column of a closed Workbook - How can I ascertain the last Cell with contents of a certain Column of a closed Workbook?
- Calculate the postage with the help of a Weight-/Areamatrix - A matrix contains a Weight- and AreaGraduation. How is the postage calculated by the Input of both factors?
- Delete the Names without destroying the Formulas - How can I delete the AreaNames in a Worksheet without getting an Error-Message in the Formulas with References to this Names?
- Determine the Value of the Cell next to the maximum Value - How can I ascertain the Value of the Cell left to the Cell with the maximum Value of a set Column?
- Access on an ArticleList via VLOOKUP() - How can I access on an ArticleList with the VLOOKUP()-Formula? After this, the RecordSets should be taken on a SalesList by pressing a Button.
- Calculation of marks by means of a Point-Table - How can I set the marks (school) in relation to the maximum score?
- Recognize References by means of a Headline - How can I fit in references to Worksheets whose names are in the Headlines, in a Cover?
- WorkingHoursWorksheet and VLOOKUP-Formula - In a Workbook I have created Worksheets for each day in a month including the working Begin and End Time of the employees. In this Workbook also exists an employee summary.
- Enter Sum formula at the bottom of a list of number values. - How can I make VBA enter a Sum formula below the last occupied cell of Column C? The first Value is in Cell C1 and the sequence of numbers has spaces.
- Separate the ZIP code from the country - How would I separate the ZIP code from a country name when both are in the same cell?
- Construct an array formula using VBA - How can I construct an array formula using VBA?
- Count the occurrences of a year in a list of dates. - How can I count how many times a given calendar year exists in a column of dates?
- Insert a variable cell reference in a formula - Using VBA, how can I insert a variable cell reference in a formula?
- Separate the first 5 Symbols after a "+" - How can I save each of the first 5 Symbols after a Plus in a long String in Variables and enter them in a Worksheet afterwards?
- Reverse the Minus - When I import TextFiles many Cells have an added minus at the end, How can I turn these Strings into negative Values?
- Enter the Ranks in a Worksheet with VBA - How are the first 10 Ranks of the Values in Column A entered in the Worksheet with VBA?
- Prevent the Error Entry #DIV/0! - How can I prevent the Error Entry #DIV/0! ?
- Determine the Value in the Cell beside the MaximumValue - How can I determine the Value of a Cell to the right of the Cell with the Maximum Value?
- Worksheet Name in Formula - Is there a possibility to have the Worksheet Name as a Function or Variable in a Cell so the contents are made topical after changing the name of the cell?
- Sum from the active Cell - How can I determine the current Cursor Position with VBA and then ascertain the Sum of all Cell Values above or below this active Cell?
- Add till the topical Date - How can I add the values of the Dates until it reaches the date that corresponds with the one at the top?
- Access a closed Workbook with the Indirect-Function - Suppose you have a title with the name "Test", and you want to link to a workbook of that name. You cannot use Indirect because it cannot be used on a closed workbook.
- Determine the Cell contents in the second Sheet - How can I determine the contents of a Cell in a second Worksheet, assuming the same Column, but a different Row?
- Change a String into Value - How can I change a NumberString with a point as Decimal Separator into a Number?
- Save Formulas with Cell Addresses - How can I save all Formulas of a Worksheet with Cell Addresses to a TextFile?
- Undocumented DATEDIF function - How can I include the nondocumented DATEDIF Function in in a Macro?
- Accessing a closed workbook through VBA - How can I access a closed Workbook using VBA?
- VLOOKUP() on a closed Workbook - How can I use VLOOKUP with criteria from the active worksheet to query a closed Workbook using VBA?
- Sum of filtered Rows - I want the Sum of a Column created after Autofiltering in a worksheet. However, the SUM() formula always returns a value of all the Rows, not just the filtered one. What is wrong?
- Disable the #NA Error-Message - How can I disable the #NA Errors, but keep the other error-checks visible (e.g. #VALUE)?
- Julian Date Formatting - How do I show the Julian Date in the "yyddd" format?
- Sum, if larger than Cell A1 - How can I sum the numbers that are larger than the number in Cell A1?
- Conditional Sum - Iīve got a Worksheet with the Columns "item" and "number". Is it possible to calculate the sum of the numbers corresponding to the items that contain a specific word? "number".
- View formulas and values - How can I view the formula =8*9+26 in A1, and the result of 98 in another cell?
- Sheet reference formulas - Formulas in range B2:M20 serve to sum the values stored in the accompanying 12 monthly worksheets.
- Dynamic sheet name in VLOOKUP & INDIRECT functions - A VLOOKUP with INDIRECT formula returns a value from different worksheets, based on values in criteria cells.
- Intersecting values returned - Two examples of returning an intersecting value in a table, using named ranges in the formulas.
- INDEX MATCH to identify maximum corresponding value - Return the value in column A that corresponds to the maximum value in column B.
- Alternative analyses - Example: Efficiency analysis and cost effective analysis for the purchase of a house.
- INDEX MATCH MATCH for intersecting cell value - In cell C1, an INDEX MATCH MATCH formula returns the value that is in the intersecting cell in the table on Sheet2, based on the column header criteria value in cell A2 and the row header value in cell B2.
- Transpose a table - The horizontal arrangement of the table on Sheet2 is transposed to a vertical arrangement on Sheet1 using INDEX MATCH formulas.
- Interest based on loan payments - Table of interest in column C for the payoff of a loan with varying payment amounts made each month.
- Round to nearest given increment - Using the ROUND function, return a number rounded to the nearest .05.
- Example of NetWorkday function - Example of the NetWorkdays function is in cell D1. It considers the number of days between the start and end dates, excluding holidays and weekends. Requires activation of the Analysis ToolPak add-in.
- Date calculated by years - The number of years in cell B2 is to be added to the date in cell A2, to return a future date in cell C2.
- Easter date - On Sheet1, the formula in cell B1 calculates the date Easter falls on, for the year displayed in cell A1.
- DSUM and array formula worksheet functions and VBA - A DSUM and array formula are entered natively as worksheet functions, and repeated as VBA macros.
- Data validation for unique value length - Data in column A must be 10 characters long, and not a duplicate value elsewhere in column A.
- Conditional formatting, 3 conditions - With Conditional Formatting, cell values in column C smaller than 5% are to be shaded green; between 5% and 10% shaded yellow; and more than 10% shaded red.
- Conditional format - With the help of Conditional Formatting, the latest (highest) date is displayed in column B, and the corresponding value for that row is displayed in column A.
- Evaluate a cell format - Evaluate whether a number in a cell is entered as text or as a number.
- Data validated named range with VLOOKUP formulas - Cell A9 of Sheet1 is data validated with a named range that is the table on Sheet2. Select a Name from the drop down list and the cells below it will populate with the relevant address information.
- SUMIF to total by day - The SUMIF formula in column G of Sheet1 serves to total the hours worked per day from the table in range A1:D9 of Sheet1.
- Examples of MATCH and LOOKUP formulas - Column F of Sheet1 is matched with source values in column A of Sheet2 to return values in column A and column H of Sheet1, using MATCH and VLOOKUP functions.
- Examples of INDIRECT - Two examples of INDIRECT - - one with a MATCH function, and one with a VLOOKUP function.
- Examples of range reference worksheet functions - Three formula composition approaches to arrive at the same result, using hard-entered criteria, or criteria based on cell values, or based on named ranges.
- Conditional Format of odd numbers - Using Conditional Formatting, odd numbers in column A will be formatted as a red-shaded cell.
- Alphabet letter converted to number - Formula that can translate each letter of a value string into that letter's position in the alphabet.
- Degrees, Minutes, and Seconds displayed in custom format - Convert separate values for Degrees, Minutes, and Seconds into a decimal factor using the formula
=A2+B2/60+C2/3600 and into a single value string using a custom format ( [h]° mm' ss.00\" ).
- Disallow certain characters using Data Validation - If a cell in column C contains "B", the corresponding cell in column F will not accept "F" or "T".
- Duplicate entries disallowed except for certain characters. - Using Data Validation, no duplicate entries may be made except a period (".") or a semicolon (";").
- Data validation controls maximum allowed formula result - Cell G1 contains the formula =E1+F1. Cells E1 and F1 are data validated such that the number in G1 may not exceed 5.
- Dynamic data validation - Items in one cell's data-validated drop-down list are made available depending the item selected from another cell's data validated drop-down list.
- Sum numbers in named ranges over multiple sheets. - The SUM and INDIRECT functions are utilized for referring to named ranges across worksheets.
- Return value corresponding to calendar week number - The value in cell B2 of Sheet1 is to appear in row 2 of Sheet2 in the column of Sheet2 that corresponds to the week number in cell B1 of Sheet1.
- Conditional formatting for duplicate values - Column A is conditionally formatted so that duplicate values are highlighted.
- Data Validation to set maximum character length in cells. - In column A, Data Validation is employed to limit cells to a maximum length of 35 characters.
- LOOKUP formula with ROUND - The formula in cell B1 looks up the value in cell A1, and rounds the LOOKUP value to one decimal.
- Two-decimalized rounding to the .09th. - Values are rounded, rounded up, and rounded down to a two-decimalized .09th.
- MATCH function identifies presence or absence of value in another range. - The formulas in columns C and D identify which values in column A are not found in column B, and which values in column B are not found in column A.
- Entry in one cell returns LOOKUP value from table - Example of a VLOOKUP formula, where the value in A1 is entered, and its corresponding value is returned in A2, from the table on the "Data" sheet.
- VLOOKUP for multiple column data - By entering an "x" in column B of Sheet1, the dimensions of the corresponding Product in column A shall be displayed in their respective columns, via a VLOOKUP formula from the table on the "Data" sheet.
- RANK utilized to allocate seats based on political party delegate count - Seats for political party delegates are allocated based on delegate count and seat availability.
- Conditional Format example for cell value - When a cell's value is "5 dollars" (without the quotes), Conditional Formatting will format the cell.
- Base date added to by 33 1/3 years - Megaformula returns a future date as does the result of a separate four-step series of formulas.
- LOOKUP and INDEX functions return prices for combination of items - Using a table for LOOKUP and INDEX functions, a combination of construction materials is specified in order to obtain a per-unit and final cost.
- Running balance - Simple example of a running balance based on Income and Expense entries.
- Return defined set of last record(s) from list - Formulas in range D2:E6 return the last 5 records from the lists in columns A and B.
- ActiveX ComboBox properties return LOOKUP value - Selecting a ComboBox item will return a corresponding value in a linked cell from the source table.
- NETWORKDAYS for number of days worked by employees - The number of workdays per employee is calculated, considering which weekdays the employee did not work within specified start and end dates.
- ROUND and AVERAGE predict future data based on historical data - Numbers are predicted for future months in current year based on productivity to date in current year and average for same calendar month from previous year.
- FORECAST function to help predict future productivity based on historical data - ROUND with nested FORECAST formula offers one approach to predicting future productivity based on actual historical data.
- Data Validation sets limits on length of cell value - Values in column A are required by Data Validation to be between 2 and 10 characters in length.
- Return value at given row interval - Return the value of every 20th cell in a range.
- Rankings of competitors based on their results over several dates - Rankings of competitors based on their results over several dates.
- SUMIF to total numbers for a given Item - Using a SUMIF formula, each Item's numbers from a separate table are totaled.
- VLOOKUP returns corresponding value for Item in adjacent column. - A VLOOKUP formula in one sheet shall reference the source table on another sheet to return the corresponding value for criteria being looked up.
- Proper sequence of values is maintained when lead cell's value is changed - A value sequence is maintained in a range when the lead cell's value is changed.
- Conditional Formatting separates value changes in a list - A sorted list of names is separated by a black line via Conditional Formatting.
- Conditional Formatting indicates a numeric value as being greater than another numeric value - Using Conditional Formatting, prices for an Item are compared between two stores. When a price in store #2 is found to be greater than a price for the same Item, that higher price is shaded green.
- VLOOKUP from ActiveX ComboBox selection - A ComboBox's selected value is returned to a linked cell which is the precedent for two other cells containing VLOOKUP formulas, pointing to a table on a separate worksheet.
- Determine minimum and maximum values in a range - Return header values associated with minimum and maximum values in a range.
- Sum numbers between and including start and end points - Items sold per month are summed based on any start and end month period the user defines.
- Conditional Formatting to identify duplicate values - Cells in column A that contain duplicate values are conditionally formatted to be shaded yellow.
- Negative numbers are hidden by Conditional Formatting - Negative numbers are conditionally formatted for white font color, making them appear invisible.
- Allow entry of positive whole numbers only - In column A, only whole positive numbers greater than zero are allowed.
- COUNTIF function scenario examples - Several example applications of the COUNTIF function, including accounting for wildcards.
- Accumulation maximum - Interval points are marked with an accumulation of prior value plus a factor value for that Interval point, to a predetermined maximum.
- NETWORKDAYS and EOMONTH functions example - How can I determine this month's count of net workdays (not including weekends), this month's count of remaining net workdays, and a percentage comparison of the two?
- VLOOKUP using OptionButton - Toggle between different assembly costs using embedded OptionButtons to define VLOOKUP formulas.
- MATCH function to search value in multiple ranges - I have a list of values in column A. How can I evaluate each value such that if it exists in either of two other ranges in the worksheet, that value shall be noted as "Current" by a formula in column B?
- Subtotal visible filtered records. - What formula can I employ to show me subtotals of visible data in filtered fields?
- VLOOKUP with data validation named range - How can I set up two cells, one with data validation and the other with a lookup formula, that relies on data from a table on another worksheet?
- Sum numbers for each weekday from source table. - I have a long list of dates in column A and numbers associated with those dates in column C. How can I build a table that sums all the numbers for each of the 7 weekdays based on those dates?
- Intersecting value in table based on two numeric range factors. - How can I devise a formula with named ranges to determine freight charges based on two known factors of weight and distance, from a table on another sheet?
- Conditional Formatting for duplicate values across columns - How can I format cells when their value is the same as the value of a cell in another column? I do not want to format duplicate values within the same column, only for duplicates when found in other columns.
- Multiple COUNTIF functions return vertically aligned values - How can I place one formula in a cell that counts the quantity of several criteria values, and displays the return values in a vertical fashion, aligned flush left in one cell?
- Graphic display in conditionally formatted cell - How can I employ a worksheet formula and conditional formatting to display a happy face, or sad face, or blank face depending on the comparison of two values?
- Comparison of ActiveX versus Forms ComboBox embedded object - Please show me a comparison of how to return an index or actual value in a worksheet cell that reflects the selected value in a Forms ComboBox and an ActiveX embedded ComboBox.
- Embedded OptionButton selections trigger mathematical operations - I want to put 3 OptionButtons on my spreadsheet, and caption each with a different number. When one of the OptionButtons is selected, I want that caption to be multiplied by a base number to give me a final result. How can I do this without using VBA?
- Sum of intersecting cells - I have a large table of data on Sheet1 with months listed across row 1 and Names listed down column A. What formula would return the sum of numbers found in specified intersecting cells?
- ActiveX Calendar control example - Please show me how to display the date in a worksheet cell that is selected by an embedded Calendar control.
- Annualized monthly data considering leap years - What formula can I employ to annualize monthly data, taking leap year into account for any given year?
- VLOOKUP criteria as concatenated values - How can I employ two cell values as a concatenated single argument for the VLOOKUP function?
- Formulas for holiday dates - How can I compile a list of American holidays and their dates?
- Intersecting value from table based on row and column header criteria - I have a list of cities and their weather data from several dates. How can I display one of the city's data for all dates by selecting its name from a data validation list?
- Return ranked values in sequential and transposed order - How can I utilize INDEX, MATCH, VLOOKUP, and INDIRECT functions to return the ranked and displayed order of listed values, and also transpose their display from vertical to horizontal?
- VLOOKUP from named range on separate sheet - How can I use data validation to list the names in a table on another sheet, and populate cells with related information from fields in that other worksheet's table?
- VLOOKUP for daily activity based on weekday - How can I return the day's activity for all 7 weekdays for the entire year, based on a lookup table located on another worksheet?
- Rank by sum of least differences - How can use the Rank function to determine the winner of a series of bets on a race or election, by summing the differences between wagered guess and actual result?
- RANK and INDEX MATCH functions to view data - How can I compose two formulas, one to rank a list, and the other to list the associated names by rank?
- Negative time - Negative time is calculated in cell C2 without consideration of the 1904 date reference. The result from the formula is obtained by combining the formula with the custom format of "-"h:mm .
- Sum sets of numbers based on precedent digit prefix - How can I sum sets of numbers that are each grouped by a sequenced prefix whole number?
- Conditional Formatting - every other row - How can I alternate the shading of every other row in my worksheet?
- Conditional Formatting - minimum and maximum - How can I mark the respective cells in a column that hold the minimum and maximum values?
- Conditional Formatting - current date - For a list of dates, how can I mark the cell that holds today's date, and the cells that hold dates which are 3 days before and 3 days after today's date?
- Conditional Formatting - Easter - How can I mark the cell that holds the date Easter falls on for a given year?
- Conditional Formatting - current calendar week - For a list of all 365 annual dates in column A, how can I mark the 7 which fall within the calendar week of the current date?
- Conditional Formatting - excess aggregate value - I have a table of numeric data in 3 columns. How can I mark the records whose aggregate value (all 3 values multiplied together) exceeds a pre-set level?
- Conditional Formatting - locked cells - With the help of a User Defined Function, how can I employ Conditional Formatting to identify which cells in a range have their Locked property set to True?
- "CELL" function example: Address - Please show me an example of how to apply the Cell function's "Address" info_type argument.
- "CELL" function example: Width - Please show me an example of how to apply the Cell function's "Width" info_type argument.
- "CELL" function example: Filename - Please show me an example of how to apply the Cell function's "Filename" info_type argument.
- "CELL" function example: Color - Please show me an example of how to apply the Cell function's "Color" info_type argument.
- "CELL" function example: Format - Please show me an example of how to apply the Cell function's "Format" info_type argument.
- "CELL" function example: Contents - Please show me an example of how to apply the Cell function's "Contents" info_type argument.
- "CELL" function example: Parentheses - Please show me an example of how to apply the Cell function's "Parentheses" info_type argument.
- "CELL" function example: Prefix - Please show me an example of how to apply the Cell function's "Prefix" info_type argument.
- "CELL" function example: Protect - Please show me an example of how to apply the Cell function's "Protect" info_type argument.
- "CELL" function example: Type - Please show me an example of how to apply the Cell function's "Type" info_type argument.
- "CELL" function example: Column - Please show me an example of how to apply the Cell function's "Column" info_type argument.
- "CELL" function example: Row - Please show me an example of how to apply the Cell function's "Row" info_type argument.
- Comparison of multiple relative ranges - How can I compare cells of relative position in two separate ranges to verify that they hold the same values, and if different values are found, conditionally format the cell of a third relative position?
- Wildcard criteria for VLOOKUP formulas - How can I use wildcard characters in my criteria values to help return VLOOKUP results?
Back to top
Click Here to Order!
Date & Time
- Calculations with one thousandth fractions of seconds - Calculations with values in the area of tenthousandth of a second are to be done.
- Delete all cells with today's date - How can I delete all rows with a date earlier than today?
- Search and copy date values - Dates are to be copied in an area with a start and end value inquired by a input box
- Take off the actual passed time from an entire time - How can I take off the from the start till actual passed time from a set entire time?
- Enter Starttime and Interim with a Button-press - How can I enter a Starttime in a Cell when I press a Button? After the next Button-press the differences of the current time to the Starttime as well as to the last interim should be given.
- Create day sheets of a month without weekends and holidays - How can I create a Worksheet in which the weekends and holidays are absent, for every day of the month in the current Workbbook ? The day sheets should be named with the date.
- Verify that a TextBox entry is a valid date - In a userform TextBox, verify that a number entered in "DDMMYY" syntax is in fact a valid date.
- Fix point in a given period - I make Time-Inputs like "11:12 till 16:15". Whenever the fix point 12:00 exists in the given period, an "X" should be shown in an other Cell.
- Distribute Data from a List of the day to day sheets - How can I distribute Data from a List of the day (date in Column A) to separate day sheets. The names of the Worksheets correspond to the respective date.
- Show the time in the Format hh:mm:000 - How can I show fractions of seconds in TimeFormat, so for example: 12:05:456?
- Enter the holiday according to the Table in the holiday-list - I have the EmployeeName, the HolidayStart and the HolidayEnd in a Worksheet. How can I color the holidays of each employee in the following month sheets?
- Dynamic working plan for a month - How can I create a dynamic working plan for a month? After entering the year or the month the list of the day should be generated automatically. Weekends and holidays - the flexible, too - should be marked.
- Enter the time difference or the time debit or an EmptyString - What Formula do I need to get the following result: - If A1 = "" then "" - If A1 = "Holiday", "Ill" etc. then "Holiday", "Ill" etc. - If A1 = 06:00 or something like
- Create a 3-Shift-WorkingPlan according to set Data - How can I create a 3-Shift-WorkingPlan considering variable free days and marking the weekends?
- Ascertain the working hours of a Personal-Number and a day - How can I ascertain the hours of a certain personal-number on a certain day with the help of a Month-Working-Worksheet?
- Calculation of the weekly working hours - How can I calculate the working hours of a week in a Worksheet considering the breaks and the regular working hours?
- Working hours considering the breaks and regular working hours - How can I automatically view the plus or minus of the working hours compared with the regular working hours in a Worksheet when I enter the start- and end-time of the
- List the week numbers of the year based on the month and year. - How can I create a list of the week numbers of the year, which updates itself when a new month or year value is entered, and highlights Saturdays and Sundays?
- Determine overtime hours worked, excluding regular time. - How can I extract the hours of overtime from total time worked?
- Choose the date and time in a Calendar-Control? - From my first userform, how can I call a second userform from which to select date and time values, and display those values on label controls in my first userform?
- List and add the days of months in a set period - How are the days of a month listed and added into a Worksheet proceeding from a Start- and End date with the current name of the month?
- Next calendar week determined when workbook opens - When this workbook opens, the calendar week (beginning Sunday) of the week after the current week shall be determined with a User Defined Function.
- Evaluate stamp-cards - How can I calculate the working hours of a day on the basis of the evaluation of stamp-cards?
- Set the BackgroundColour according to the date - How can I change the BackgroundColour of a Date-series that must be set?
- Birthdays of a set month in a new Worksheet - How can I transfer the birthdays of a certain month from a Personal List into a new Worksheet?
- Calendar of the working hours of a year - How can I create a calendar of the working hours of a year that considers my individual Start- and Breaktimes?
- Create a calendar with marked weekends and holidays - How can I create a new Workbook in which the first Worksheet contains a calendar with marked holidays and weekends?
- Create a workbook calendar with monthly sheets. - How can I create a complete annual calendar with 12 monthly sheets, conditionally formatting weekends and holidays?
- Test the Start- and Enddate and mark it - How can I make Excel ask for a Start- and Enddate via an InputBox and then mark both dates colored?
- Search for the Date and enter the Value - How can I find a Date in Column 1 of Worksheet 2 that is put in Cell A1 of Worksheet 1? In the Cell to the right of the found Date a Value should be entered.
- Turn Decimal Numbers into Time, and Reverse - How can I change DecimalNumbers into Times and vice versa and add them?
- Enforce a valid date input in a TextBox. - How can a Text Box be made to only accept date values?
- Number of a certain Weekday in the Month - How can I determine how often a certain Weekday is in a set Month?
- Enter the Weekdays Mon - Fri in a Worksheet - I want to enter the Weekdays Mon - Fri in Column A with an empty Cell following and the Date belonging to them in Column B for a pre-determined period. How can I do this with?
- Mark in dependence on the quarter - How does the Input of a Date in Column A affect Columns A:F of the corresponding Row that is marked differently, in dependence on the quarter?
- Simplified Time Input - I have to enter a great number of Times in a Column. But the Input of the colon with, for example "12:15", hinders a fast Input. How can I simplify this Input?
- Change font color based on Date - How can I change the font color of a Cell to Red if the date is greater than today?
- Times Format MM.SS.### - How do I display Time data in the format of Minutes.Seconds.ThousandthSeconds?
- Conditional subtraction of time - Increments of time shall be subtracted from a precedent cell depending on the time value of that cell.
- Workday hours - Determine the difference between start and end work times based on a standard work day.
- List dates and week number - Enter all dates for the current year in column A, and their corresponding week number in column B.
- Dynamic calendar of quarters and years - Entry of today's date in one cell triggers a dynamic identification of future quarters and years.
- Divide time - The current time is displayed in an InputBox, or you can enter your own time, and that time shall be divided by 4.
- Year is extracted from text string - The text string in cell A2 shall have the year extracted in two ways: Formula UDF, and macro.
- Annual workday calendar - Create a 12-month workday calendar with options to include or exclude weekdays and holidays.
- DIFF function calculates negative time - Negative time potentially calculated, using the DIFF function.
- Year, Month, and Date differences calculated using the DATEDIF function - Using the DATEDIF function, the differences in years, months, and days are calculated for two dates.
- Date formula for birthday - Determine if the person whose birthdate is in column C is celebrating a birthday today.
- Time elapsed and wages earned - Simple earnings computation based on hourly wages multiplied by time worked.
- Calculate Regular and Overtime work hours - Simple computation of Regular time worked and Overtime worked, based on start and end working times, considering a defined "Standard workday" factor.
- Rounding of elapsed time - Calculate the rounding of actual elapsed time such that at or after the first 15 minutes of the hour, the elapsed time will be rounded to the next half-hour.
- Elapsed times in several categories are calculated between start and end dates - Using formulas with a Forms button or pressing F9 to recalculate, a pair of start and end dates and times are calculated for elapsed and remaining years, months, days, hours, minutes, and seconds.
- Identify earned wages for night work and day work on the same shift - Identify earned wages for night work and day work on the same shift, when night time wages have an additional hourly wage incentive.
- Return workday end time based on work start time, break time, and regular workday hours parameters - A formula shall serve as a guide for suggesting what the workday's "End work" time should be, based on the "Start work" time, considering rules for Break time what hours constitute a Regular work day.
- Scheduled worktime is marked by Conditional Formatting - Conditional Formatting marks worktime for cells in columns headed by hours of the day.
- Days overdue - Determine if today's date is past the allowable number of days for a payment to be made.
- Word replaced by time value - A word ("Hours" in this example) is replaced with a time value for two formulas - - one being a native Excel worksheet formula and the other being a VBA-driven user defined function.
- DATE formula returns date based on cell values, one being from a Forms ComboBox - A Forms ComboBox returns a month number, which with manually entered values for year and day, supply the DATE formula with enough arguments to return the associated date.
- Total of hours worked on weekends and weekdays - A dynamic calendar sums the number of hours worked on weekends and weekdays.
- Conditionally format weekend dates in given month and year - By specifying a year in one cell and a month in another, how can I list all the dates in that month, what day of the week those dates fall on, and conditionally format Saturday and Sunday dates?
- Incrementally display time in cell and create HTML file - How can I display and update the time every second in cell A1, and at a specified time interval create and update an HTML file whose text shall be the time it was last saved?
- Automatic time format as HH:MM:SS - How can I enter numbers for hours, minutes, and seconds without the colon separators, and have Excel display my entry in HH:MM:SS format?
- Calculate time in hundredths of a second - How can I sum a list of elapsed times represented in seconds, and display that total in minutes, seconds, and hundredths of seconds?
- Determine peak and off-peak hours in 7-day calendar week - How can I determine through formulas if a certain date and time on any day of the week fall within guidelines for peak or off-peak status?
- Controlled work time calculation - How can I return the total work time for employees based on controlled start and end times?
- Speed per hour - How can I determine the speed in miles per hour based on elapsed time and distance traveled?
- Start and end times in one table are marked by shaded cells in another table - How can I represent a table of employees' time schedules in a separate table that uses Conditional Formatting to color-shade cells based on those times detailed in the employees' time table?
- Time calculations for hours, minutes, and seconds - How can I look at the same time of day using two sets of Excel's time calculation formulas, and arrive at the same value for percent of day that has elapsed?
Back to top
Click Here to Order!
Dialog Boxes and UserForms
- Display Office Assistant with instructed text. - Display the office assistant with help text in a UserForm with button click
- Enter search phrases in UserForm, import found location - I have a range of cells that contain text values which are hyperlinked to the names of picture files on my hard drive. How can I employ a userform to search for such a text value, and if found, import the associated picture object into the corresponding cell address of another worksheet?
- Proof check boxes of a selected area - The checkboxes in area A1:F10 are to be proofed for their values
- Insert contents from UserForm-TextBox with graphic hyperlinks - With a UserForm, the TextBox contents together with hyperlinks to selected graphics is to be transferred to a worksheet
- Read and deleted values without duplicates in UserForm - Column A is to be imported in a UserForm-ComboBox without duplicates
- Populate ComboBox with unique values - A userform ComboBox shall be populated with unique items by a list in column A that contains duplicates. When an item is selected in the ComboBox, a calculation shall be made on numbers in column B associated with all duplicate values of that ComboBox item.
- Read, search, edit, display value in a ComboBox - Search phrases from column F are to be imported in a ComboBox. When selecting the phrase in the ComboBox it shall be transferred in a TextBox for editing
- Search for article number with criteria and copy records. - The article numbers of sheet "Criteria" shall be searched in sheet "Data" in column A. When found, copy files into sheet "Found". If not found, copy to sheet "NotFound"
- Enter list box value in active cell - When clicking on a ListBox value, the value is to be inserted in the active cell.
- Determine price with selecting article in ComboBox - Values from column A in sheet 2 are to be inserted in the ComboBox. The corresponding price of the selected value is to be displayed in cell E4
- Jump from text box to text box with TAB key - How can I move from a sheet to a TextBox with the TAB key.
- List duplicate values in a UserForm-ComboBox - Duplicate values in a sheet are to be listed in a UserForm-ComboBox
- Set up background color of an object in a UserForm - The background color of an image object is to be set through a button
- Insert file names in UserForm-ComboBox and open file - Values from column A are to be imported in a ComboBox The file is opened via a hyperlink.
- Hide/Unhide columns with UserForm selection - With a UserForm, only the unhidden columns are to be set.
- Proof for duplicates before entry in sheet - Before entering a value from a UserForm-TextBox into a sheet, check to see if the value already exists.
- Select and save sheets in UserForm-ListBox - Worksheets, selected in a UserForm-ListBox are to be saved in a new workbook
- Enter value in appropriate active UserForm-TextBox - A value is to be inserted in the currently active TextBox of a UserForm.
- Select worksheet with UserForm-ComboBox. - Selection of sheet with UserForm-ComboBox. Macro executed with CTRL-A
- Search for table with ComboBox selection - The values from columns A:B in sheet 1 are to be displayed in a ComboBox. The selected machine and its price is to be displayed in sheet 2
- Display number word synchronized to TextBox entry - Simultaneous to entries in a UserForm-TextBox, the corresponding number word is to be displayed in a second TextBox
- Limit entry in worksheet text box - After entering 3 symbols in the TextBox, they shall be written in the first free cell in column A. After that, the TextBox is to be emptied and gets back the focus.
- Total of all values depending on combo box entry - Display total of all values which match the name in ComboBox of column A in cell D1
- Sort and move date in ListBox - The values form column A are to be imported in a UserForm-ListBox. In the UserForm, the values are to be moved from one ListBox to another and sorted.
- Selection of cells according to ComboBox - How can I integrate a sequence of cell addresses in a ComboBox and go to them after selecting in the ComboBox
- Insert date from TextBox in first available space - A date from a UserForm-TextBox is to be inserted in the first available cell of a date sequence, if CheckBox is activated
- Mark date sequence according to UserForm-TextBox - A date sequence resulting from a UserForm-TextBox is generated in Column A.
- Display UserForm for 5 seconds - How can I have a UserForm display for five seconds?
- Build own class for UserForm-TextBox - In a UserForm with four TextBoxes is -after entering the eighth digit- to be checked it entry is a valid date. All TextBoxes are to be linked with a common
- Display values in two column ListBox left and right aligned - Columns A and C in a UserForm-ListBox are to be aligned left, column B is to be aligned right.
- Save entered value in TextBox - How can I retain the value entered in a UserForm textbox so that it is displayed with the next opening of the UserForm?
- Generate date sequence from TextBox entries - How can I generate a number sequence from entries in a TextBox (start and end date)?
- Proof UserForm option fields - If in a UserForm the second option field in the first group field and the third option field in the second group field is activated, a new worksheet is to be
- Check box with check off signs instead of crosses. - How can I integrate a check box in a sheet with check marks instead of crosses?
- Value from ComboBox in TextBox , then search for value in table - A value is to be selected in a 3 column ComboBox. This value is to be transferred to a TextBox and searched in sheet 2 column C and then highlighted.
- Fill a list box depending on a ComboBox - How can I fill a ListBox in a UserForm depending on the selection of the ComboBox?
- Change focus and selection in two text boxes - Focus shall change with entries in between two text boxes
- Fill second list box depending on first - A second ListBox is to be filled depending on a selection from the first ListBox
- Synchronize option fields in various worksheets - Synchronize the option fields in all worksheets
- Conditional drop down fields in dialog sheet - Depending of a selection in the first drop down field, the second drop down field is to be filled. After the selection, the respective values from sheet2 are to be displayed in the cell range in column E
- Selected rows from UserForm-ListBox in new table - Columns C:D are to be listed in a UserForm-ListBox. The selected rows are to be transferred completely in a second workbook. Multi-selection is to be enabled.
- Transmit two dimensional array to user form combo box. - Fill a two dimensional array with a string and transfer to a user form combo box
- Sort multi-column user form list box. - Sort two columns in a list box by the order of column 1 and column 2
- Entry of a value from UserForm-TextBox in active cell - A selected value from the UserForm-ComboBox shall be entered in the active cell
- Stop activation of several check boxes - Determine the position of the clicked check box and double clicking in a cell is to be avoided. No option fields are to be used.
- Close dialog after 3 seconds - The dialog with a yes-no question is to be displayed. If the user does not come to a decision after the first 3 seconds, the yes button is automatically chosen.
- Command buttons in various tables with a macro. - How can I control a series of command buttons from the control tool box through a single macro?
- Change size of a UserForm including elements - How can I change the width of a UserForm including its elements during run time?
- Entry limitation, prevent entry of letters - The number of digits of an entry in a worksheet Text-Box is to be limited. Limitation also on letters only.
- Delete duplicates in UserForm-ListBox - How can I deleted duplicate entries in a UserForm-ListBox
- read column content in UserForm-TextBox with scroll bar - The content of column A shall be imported in a UserForm-TextBox with vertical scroll bars
- Inquire search phrase and found row in user form text box - Require a search phrase and search in sheet. Open and display data of row with found phrase in user form text box
- Entry limitation for text boxes - How can I determine various entry limitations in a three UserForm-TextBoxes
- Delete elements in a UserForm box with DEL key - How can I delete elements in a UserForm-ListBox with the DEL key
- Worksheet TextBox values depending on center field - Values of the TextBoxes are to be filled depending on the value of the center field with the cell contents from columns A:C
- Open workbooks in a tree view control element - The opened workbooks and their worksheets are to be imported in a TreeView-Control element. With clicking on the element, the respective object is to be
- Animated gif graphic in UserForm - How can I get a gif graphic to be animated in a UserForm?
- Maximize user form based on current screen resolution - Maximize a user form after verification of resolution
- Search value from user form text box and copy found location - Search a number in column 1 and enter it in user form text box. The row with found entry shall be copied in a new workbook up to column R. Search in table 1 to 5
- Search each word of a sentence in an index list - Every word in a support question shall be searched in an index list and the corresponding answer is to be displayed.
- Values from dialog check box into and from table - How can I enter the value of a dialog check box into a table and open it from the table with the next dialog.
- Activate 10 UserForm-TextBoxes in a series - How can I activate and deactivate 20 UserForm-TextBoxes with a button click?
- Test for valid entry of date and time in TextBox - In a userform TextBox, verify the correct syntax was used to enter a date and time.
- Sort the UserForm-ListBox-Inputs - How can I sort the Inputs of a UserForm-ListBox rising?
- Ascertain the position of the calling Button - How can I ascertain the left top corner under the calling Button as well as the Label of the Button?
- Show the current time in a UserForm - How can I show the current time in a UserForm?
- Address UserForm ComboBoxes as Collection Objects - How can I include a series of UserForm ComboBoxes in a Collection Object and address the Elements as such?
- Close the Workbook when there is no Input in the TextBox - How is a Workbook automatically closed when the User enters nothing in a UserForm-TextBox for 1 minute?
- Scroll through a UserForm-ListBox at intervals of two seconds - How can I scroll to the next UserForm-ListBox-Input at intervals of two seconds ?
- Copy a Group of Option-Fields and assign it to a Macro - How can I copy a Group-Field with Option-Fields in a second Worksheet and assign a Macro to the Option-Fields?
- Value from Column 2 of a poly-columnic ComboBox as general Value - A general Value of a poly-columnic UserForm-ComboBox is the Value from the first Column of the ListIndex shown. How is the Value from the second Column shown, when I call UserForm
- Search for the Values from the UserForm-ComboBox and copy the place - How can I take the Values of Column A on a UserForm-ComboBox, search for the selected Value in the Worksheet and copy the place found in a second Worksheet?
- Program-Control for several calling UserForms - How can I branch a Program that can be called by several UserForms depending on the calling Form?
- DM/EURO and EURO/DM-conversion with leaving a TextBox - I have two Textboxes in a UserForm, one for DM- and one for EURO- Values. When I now leave one TextBox the Value of this TextBox should be entered converted in the other TextBox.
- Choose the before selected Area after the Worksheet-ComboBox - How can I choose the before selected Worksheet-Area again, after selecting something in a Worksheet-ComboBox?
- Select an Element in a ListBox depending on an TextBox-Input - How is the first Element of a ListBox selected to fit the letter that was entered in a TextBox? When I enter more signs a further search and possible selections should be done.
- Enter Articles and Prices from a UserForm into an invoice - After a DoubleClick in an invoice form a UserForm with a multiple column ListBox, including Data of an Data Sheet, should be shown. After the selection of a RecordSet this should be entered in form.
- Fill a multiple column UserForm-ListBox and read in actual Value - How can I read in a Worksheet in a multiple column UserForm- ListBox? After the selection of a RecordSet in the ListBox, the actual Value of Column 1 should be read in a Worksheet Cell.
- Fill a UserForm-ListBox depending on the calling Column - How can I fill a UserForm-ListBox depending on the Column-Header created Button with Values?
- Read in FileNames in TabStrip-Elements - How can I read in FileNames that are entered in a Worksheet as Captions in TabStrip-Elements? After a click on a TabStrip-Element the respective File should be opened. The properties should show.
- Set the Autofilter-Criterias via UserForm - How can I set the Autofilter-Criterias via UserForm-TextBox-Inputs?
- Show a chance number centred in UserForm-ListBox - How can I view each number that was determined randomly in a UserForm-ListBox?
- Hide and unhide worksheets via userform ComboBoxes - Hide and unhide sheets by selecting their names from a userform ComboBox. The userform is called by a custom menu option loaded in the Open event, and deleted at the Close event.
- Enter Value in Cell when the CheckBox is activated - How are Values entered in the Cell next to a CheckBox when the CheckBox is activated?
- Password-Query with ShortCut-Call - How can I call a Dialog for the Password-Input with the Keycombination Ctrl+w? When the correct password is entered, the Sheet Protection of all Worksheets should be withdrawn.
- Read in and output Values in a UserForm-ComboBox - How can I read in the Values of a Column in a UserForm- ComboBox and output it after a selection directly in a Cell?
- UserForm as Progress Bar - How can I use a UserForm as a Progress Bar?
- Select Signs in a UserForm-TextBox via a ScrollBar-Runner - How can I select the single Signs of a UserForm-TextBox according to the selected ScrollBar-Values?
- Enter a Value depending on the calling Button - How can I enter a UserForm-TextBox-Value in Cells depending on the Button that calls the UserForm?
- Show the Text of the last Cell with contents in a UserForm-TextBox - How can I view the Value that is entered in the last Cell of Column A, in a UserForm-TextBox when I call a Dialog?
- Search for a Text of a UserForm-TextBox in a Worksheet - How can I search for Values of a UserForm-TextBox in a Worksheet and output the Rows found in UserForm-Labels?
- Select an Area via UserForm-RefEdit-Control - How can I select an Area via a UserForm-RefEdit-Control and set a conditional Formatting to the selected Area?
- Verify TextBoxes for numeric entries - A userform with three TextBoxes shall be evaluated for the entry of numeric values. See Sheet1 for details and a demonstration.
- Call the first out of the second UserForm - How can I call the next UserForm after the Input of a Value in a UserForm-TextBox and jump from there back to the first again?
- Add UserForm-TextBox-Values to an ListBox - After the Input of 2 numerals in a UserForm-TextBox the Value should be entered in an ListBox and the Cursor should jump to the next TextBox.
- Allow only the Input of 2 numerals in the TextBox then go to next - How can I make the Cursor jump to the next TextBox after I have entered 2 numerals in one TextBox? The Value should be entered in the Column that the TextBox-Value is in when I leave current box.
- Read in the visible Cells of an AutoFilter-Area only - How can I read in only the visible Cells of an Area using an AutoFilter and display them in the ListBox of a UserForm?
- Overwrite Value depending on Dialog-OptionFields - How can I overwrite the last Value of a List depending on a Dialog-OptionButton?
- Add a MenuItem and show a UserForm timecontrolled after a Call - How can I add an additional MenuItem to the Worksheet Menu when I open a Workbook and delete it again, when I close it? The MenuItem should show the Username for 2 seconds.
- Sheet Selection via a UserForm-ListBox with multiselect - How can I select WorksheetRegister that were selected via a UserForm-ListBox?
- Enter a Value from the UserForm-ListBox into a Worksheet - How can I enter a Value, that is selected in a UserForm-ListBox, into a Worksheet after pressing a Button?
- Take Values from the UserForm-ListBox on the ClipBoard - How can I take all selected Values of the first Column of a multiple column ListBox on the ClipBoard?
- Read out Values of first Column of a multiple column UserForm-ListBox - How can I read out all selected Values of the first Column of a multiple column ListBox in a Worksheet?
- Combine Msg- and InputBoxes and branch according to the result - How can I branch different Macros depending on Inputs in a combination of Msg- and InputBoxes?
- Macro branching according to OptionField- or ListBox-Selection - How can I branch Macros depending on OptionField- or ListBox-Click and output the Values?
- Fill a multiple column UserForm-ListBox without spaces - How can I fill a multiple column UserForm-ListBox in a way with Values of a Worksheet so that Empty Rows arenīt taken into account?
- Unhide / hide Columns according to a UserForm-ListBox-Set - How can I unhide and hide Columns of different Worksheets according to the Settings in ListBoxes of an UserForm-MultiPage- Control?
- Read Column-Headers in UserForm-ListBox - How I can read Column-Headers in a UserForm-ListBox and edit these there?
- Simulate the tabulator in the UserForm-TextBox - How can I use a tabulator in a UserForm-TextBox?
- Read SheetNames conditionally in ListBox and select Worksheets - How can I fill a ListBox with the existing SheetNames, except the Sheets "Test" and "Pattern", when I open a Workbook? The selection of one of these Sheets should be done by a press button.
- Multiply Values of two UserForm-TextBoxes - How can I output the Values of two UserForm-TextBoxes multiplied together in a third TextBox?
- Fill a TextBox depending on a ComboBox-Selection - How can I fill a TextBox of a UserForm with Values of a Worksheet depending on a Selection that was done in a ComboBox?
|
