Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Data Mask

Hi,

I want to use the data mask (data-->mask) but it is limited to 32 fields
(columns). Is there any way to overcome this boundary? Has anyone found a
free self-programmed equivalent that allows more fields?

Thanks for your help,
Kurt


Post your answer or comment

comments powered by Disqus
how do you set the number of characters for input on a certain cell or column

i knew this at one point, but when u don't use it, unfortunatly u loose it...

thx
DA

I'm wondering if there is a way to format the data you enter into cells; much like one would use an "Input mask" for entering in dates and time?

Can one create a custom "Input mask"(Not sure if this is the right terminology) for this purpose in Exel? (Like access)

Mike C.

I've been tasked with saving excel data as a .csv, then uploading the data
into an ingres database. There are numerous needs for special formatting in
the ingres database, and I'm trying to devise methods to deal with some of
them...

- a numeric field required a blank at the end of it (12345b)
- blank values in the cells that need to be converted either to null or to
-1E38
- 15 character numbers that need to stay as discrete numbers, not go to sci.
not.

Many of these seem like they could be resolved with some edit masks, but I
can't locate any decent coverage of the topic in the online help. Am I not
looking in the right place? Or is there a separate source for documentation
that I should be using?

Jeff

Hi,

I'm trying to use some sort of input mask for cell data entry. The cell is for a car registration (new style) so it will be fixed at two letters followed by two numbers a space and then three letters... AA02 AAA. Im using excel 2007.

Can anyone help me with this? It seems so simple but yet so hard...

Cheers,
Adzi

I want to set data validation on cells to only allow:

C-###-#####-#### (# is any digit)

Is there a way I can use the equivalent of an Input Mask, or do I have to
write a big long formula?

Thanks
Rob

Hi all,

I have to rebuild a database that I previously built in Excel. I was originally doubtful whether to choose Access or Excel, and in the end choose Excel so the end user could more easily filter and select data accordingly to his needs within minutes. As I had to build a very big list of data, with many fields, I ended up with 25-30 fields in a single table.
The only problem I have is the insert of data: as there are so many fields it is unconfortable to put data. Here is my question:is it possible to create one or more personalized modules (like the tool "Data Module") , in order to have something like Access' masks ?

Hope I could explain myself enough, Thank you in advance

hi guys,

(Note: excel for Mac, so VBA not really an option)

I have data where the fourth field is in multiple columns, and I need it in multiple rows:

e.g.
masks 1954 producta productb productc
coats 1543 productd
shoes 1234
hats 9876 producta productf productg productz productd

and I need it in the format:
masks 1954 producta
masks 1954 productb
masks 1954 productc
coats 1543 productd
shoes 1234
hats 9876 producta
hats 9876 productf
hats 9876 productg
hats 9876 productz
hats 9876 productd

in other words the fourth field (products) is in multiple columns, and I need the fourth field only transposed to multiple rows, and the other fields repeated.

any help appreciate (and excel for mac is very limited with VBA. prefer formulas if possible, or mac friendly vba if not)

David

Category Blah1 Blah2 Products

I have a field in my form that is used to input and retrieve data from a table. i want to put a check to this field that only 24 digits can be entered. the length cannot be longer or shorter 24 digits only. how do i put a check on it. i can set up mask for 24 digits but if someone tries to enter less than 24 digits it accpets that too.

any help appreciated.

harris

I have been attempting to tweak this VBA for a few days with no luck. http://www.ozgrid.com/VBA/mask-time-input.htm
I perform work time studies and record my stop watch reading into a spreadsheet using hh:mm:ss format. My stop watch reads in 60th of a minute. I always have to type the semi-colons ( as I enter the times into my spreadsheet which makes it a very slow data entry process. The above link gives the method to create the mask format hh:mm using VBA. Can anyone please tell me how to change the above code to allow hh:mm:ss format so I do not have to type the semi-colons when I enter the times? It is mentioned in the author's text that this can be done but I am not good enough at VBA to get it to work. Thanks for any help/advice you can provide.

I have searched the archives and FAQ, and still cannot locate an aswer to this. I apologize in advance if this is not possible or I missed the answer somewhere.

I have a form for data entry, and it is for a time study I do for physicians. I want to enter times in 24 hour format, and I enter five sequential times in 24 hour format.

My question: I want to be able to use the numbers pad to enter the times (example: 1212, 1516, 2345, etc) and not have to use the SHIFT key to enter the : symbol. Having to SHIFT : slows down data entry a lot, and I was hoping that I could somehow use a MASK (like in Access), but I have tried for four days now and cannot get it to work.

Additional Bonus Question: Each of the five times are in sequence, so if there is a way to make sure that each time is after the previous time, that would be fantastic. FYI: The times never cross the midnight hour, so there is no worry about the start time being 23:59 and the end time being 00:15.

Thanks a bunch to any help or in diecting me where to find an answer on this

Michael Lockard

hi all,

got a problem whereby when a user selects a particular month from a list, onlt related data pertaing to the month appears and the rest of information on the list is sort of masked..information not related to the selected month is not displayed on the screen..any clues, anyone?

cj

Hi!

I'm a novice at VBA. I've created a User Form for people who are not experienced with Excel and I'm having a couple of problems.

1) The form includes text boxes as well as pull-downs (combo boxes). I was wondering if there was a way to use input masks (like in Access) to prevent uses from entering inappropriate data. For example, I have several date fields. Is it possible to have my user form already have a mm/dd/yyyy format for them? Or, if that's not possible, is there some way to make the User Form beep at them? I have prompts in the labels next to the boxes, but I'm afraid it won't be enough.

2) I'm also having problems with errors that my form is producing. If you click outside of the form boundaries or hit enter in some cells, there's an error with my Ok to quit statement:

Private Sub UserForm_Click()

Private OKToQuit As Boolean ' determines if button pressed to exit

End Sub

Private Sub Cancel_Click()
OKToQuit = True
Me.Hide
End Sub

Private Sub Apply_Click()
MsgBox "Button pressed; Apply choices"
OKToQuit = True
Me.Hide
End Sub

3) As I said, I've got combo boxes, but they let you type in them. Is there a way to make this NOT happen?

4) I don't understand the use of If statements in the code. For example, I have a particular yes/no question. If the user enters YES, I'd like them to enter a date in the next box. If either NO is entered or there's no information, I'd like to have that text box turned off. Is this possible?

Again, I'm really just learningthis, so your patience is appreciated. Or, I'm happy to share this with someone if that's easier ~ please leave me your email address.

Thanks!
Marianne

Thanks for any suggestions.

Here is my problem. I have little experience with charts and am modifying a project created by someone else.

Data streams into my workbook by way of DDE. Initially, at the start of every day, there is no data in the range being referred to by my workbook names.

These dynamic workbook names are used as the series' source in my chart. Because the references are not yet valid, for lack of data, a constant warning message is displayed when my DDE links are updated. The popup is modal, stops my code, and is obviously an unacceptable problem.

Application.DisplayAlerts does not work and I would rather not use it to mask a problem even if it did work.

Here is an accurate simplification of my design.

AllData is a static named range that refers to the range A2:A65536

AllData_UsedRange is a dynamic named range that refers to the subset range of AllData that contains data. All of the data is numeric, is entered consecutively, and begins at the first cell on down. A2:A?.
=OFFSET(AllData,0,0,COUNTA(AllData))

To "solve" my problem, I modified the formula that defines AllData_UsedRangeto this:

=If(ISERROR(OFFSET(AllData,0,0,COUNTA(AllData)),0,OFFSET(AllData,0,0,COUNTA(AllData)))

If the formula is an error, return a zero. If not, return the reference.

The popup went away and joy set in until I could not get the range address of the name AllData_UsedRange in my VBA code. Even after data was streaming in.

In summary:
AllData RefersTo A2:A65536
AllData_UsedRange RefersTo the range containing data within AllData.
AllData_UsedRange is used as a source for a chart series.

How can I avoid the popup errors until the reference is valid?

Thanks!

Hi everybody,

I'm trying to create a spreadsheet (excel2002), when the user inputs a policy unique ID. Then Excel can identify the format mask of that cell and produce the result of the correct company related to that format mask.

For example

User inputs ID AG12345. It will populate ANOTHER GENERAL LTD
User inputs ID 123867. It will populate ACME

I have to identify hundreds of policies numbers to each unique company.
Anyone's help is appericated. Thanks in advance

Hello -

In column A I am trying mask ssn numbers; however, they have to be masked in that column and not in another column - can this be done?

I was told to try "xxx-xx-"&RIGHT(A2,4) -- but that puts in the information into column B and I can't get a formula to be added to the column that already has the data in it.

Please help!

Thanks

Hi Everyone,

Before I start I think we've been here before but the posts I've found through searching, while useful, haven’t exactly helped me.

I have a textbox on a userform whereby I only want to allow the user to enter 6 characters, of which the first three must be alpha characters and the last three must be numerics.

There are two approaches therefore as far as I can see.

1. Let them enter whatever they please and check the format after
2. Restrict entry into the actual textbox.

In the first case I can see one way of doing it which is basically an enormous series of checks (i.e. IF statements I guess) and throw error messages back at the user and re-present the form. I can see myself creating enormous amounts of code though so I wondered if the board members might be able to do this in a far tidier fashion than I can??

In the second case, I don’t know how to do this at all.
In the following two threads, TSTom talks about Microsoft Masked Edit.

http://www.mrexcel.com/board/viewtop...c=4674&forum=2
http://www.mrexcel.com/board/viewtop...=10170&forum=2

I do have this feature available when I right click my VBA toolbox, however, I get the same error reported as Mr Nick in the first of the two threads. Any ideas on how I get this little feature to work?

Thanks a lot folks.

Rgds
AJ

Hi there!
I'm trying to think of a better way to do this.
This is a sample of my data - I have 10 worksheets (will have 90 evenutally) holding data by account #'s and i need to summarize them based on a mask.

Is this kind of summarizing possible? If not I wouldn't even mind doing a seperate formula by Account # summary that would logically go something like this (my formula isn't working btw).

=sumif(a:a,">31000000 or Microsoft Excel - sample data.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI18=
ABCDEFGHI1sheet*1**sheet*2***sheet*3*2Jan*04**Feb*04***Account*#*Summary*3Acct#value*Acct#value****431001100200*3100110050**31000000*53100120050*3100120060**42000000*63100130060*3100130090****73100140050*3100190035**32000000*831001500500*32002100240**50000000*931001600800*32002200640****1031001700350*3200240020**33000000*1131001900950*3200260010**43000000*123200210020*330032005****133200220040*3300330040****143200240060*******153200260080*******1633003100800*******173300320050*******183300330060*******Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Is there a way to create some sort of masking sheet over a current data sheet
that, when I re-input data, can tell me if there is a discrepancy between the
two values. For example, on Data Sheet 1, I have the value 98765, and when
re-entering the data, I mistype 11234, Excel can notify me of that problem?

This is for the purposes of verifying numberic data that has been entered
into a speadsheet. Thanks!

Is there a way to create some sort of masking sheet over a current data sheet
that, when I re-input data, can tell me if there is a discrepancy between the
two values. For example, on Data Sheet 1, I have the value 98765, and when
re-entering the data, I mistype 11234, Excel can notify me of that problem?

This is for the purposes of verifying numberic data that has been entered
into a speadsheet. Thanks!

I would like to do some data entry in a spread sheet.

First example
I have a date cell and it is using the standard of 02/07/06 .
I want to make it so that when I start the cell I see the slashes and I
don't need to put them in and it goes from the 02 to the 07 without me
having to manually putting the slashes in.

Second example
I have a time cell that uses 09:28. I want to be able to put in 0928
and it separate it out without me having to put the : in manually.

Are these two examples what masking is for? This is probably in the
help somewhere and I am just not seeing it.

Thanks for your help

I have a form that I would like to make some TextBox's to be formatted for dates (having a mask for m/d/yy), and then a couple for dollar amounts $X.XX. Can someone please tell how this is done (any where it needs to be put if it is a specific placement).

I've tried:

CRF_Form.TextBox1.Value = Format(Date, "m/d/yy")

I've seen other people trying this (it doesn't change the data when entering in the data though).

Thanks.

Hi guys, me again.

Does somebody know how to mask input data in a TextBox? I'd like only dates
to be inserted so that I can use it afterwords as a filter for bringing data
from Access Database.

Thanks
Bregards

Santiago

I have never program Excel Macro before, so bare with me with all my problems.

This is what I have done so far. I can recursively go through all the subfolders in the directory and after that I have to find all the excel spreadsheet with the name of "*answers.xls". I can combine all the data if I only have one sheets. But once I have two sheets I'm not able to combine all the data into the Master sheet. I can only get the last excel spread sheet in each subfolders.

So if one subfolders have 5 excel it will only grab one of them. I know the program runs through all the excel in the directory. But it's not combining with other rows for some odd reason.

All the *answers.xls have total of 17 columns and unknown rows.

One sheets called is for the button that trigger the macro
The other sheet - "Master sheet" is for storing all the data.

I attached the master workbook and two other workbooks are the sample data I'm trying to combine into the master workbook.

123answers.xlsx
221answers.xlsx
Temp_Master_Answers.xlsm

I'm not too sure where I did it wrong.

This is my code:

Option Explicit
Sub FileListingAllFolder()

Dim pPath As String
Dim FlNm As Variant
Dim ListFNm As New Collection ' create a collection of filenames

Dim ShtCnt As Integer
Dim Sht As Integer

Dim LR As Long, NR As Long
Dim wbkOld As Workbook, wbkNew As Workbook, ws As Worksheet
Dim i As Integer

' Open folder selection
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        pPath = .SelectedItems(1)
    End With
    
    Application.WindowState = xlMinimized
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    ' Create master workbook with single sheets
    Set wbkNew = ThisWorkbook
    Set ws = wbkNew.Sheets("Master Answers") 'sheet report is built into...edit to match
    
    If MsgBox("Import new data to this report?", vbYesNo) = vbNo Then Exit Sub
    
    If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
        ws.Range("A2:A" & Rows.Count).EntireRow.ClearContents
        NR = 2
    Else
        NR = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
    End If
    
    
    ' Filling a collection of filenames (search Excel files including subdirectories)
    ' Call FlSrch(ListFNm, pPath, "*.xls", True)
    Call FlSrch(ListFNm, pPath, "answers.xls*", True)
    
    
    ' Print list to immediate debug window and as a message window
    For Each FlNm In ListFNm ' cycle for list(collection) processing
        'Do While Len(FlNm) > 0
        'Open file
            Set wbkOld = Workbooks.Open(FlNm)
        'Find last row and copy data
            Sheets(1).Activate 'Sheets(1).Activate
            LR = Range("A" & Rows.Count).End(xlUp).Row   'find the bottom row of data...change to a different
column if "A" isn't reliable for spotting this value
            Range("A2:A" & LR).EntireRow.Copy _
                ws.Range("A" & NR)
        'close file
            wbkOld.Close False
        'Next row
            NR = Range("A" & Rows.Count).End(xlUp).Row + 1
        'move file to "imported" folder
            'Name fPath & fName As fPathDone & fName         'optional
        'ready next filename
            'FlNm = Dir
        'Loop
    Next FlNm
    
    ' Print to immediate debug window and message if no file was found
    If ListFNm.Count = 0 Then
        Debug.Print "No file was found !"
        MsgBox "No file was found !"
        End
    End If
    
    Cells.Select
    Selection.EntireColumn.AutoFit
    Range("A1").Select
    ActiveSheet.Columns.AutoFit
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.WindowState = xlMaximized
    
    End
    
NextCode:
    MsgBox "You Click Cancel, and no folder selected!"

End Sub

Private Sub FlSrch(pFnd As Collection, pPath As String, pMask As String, pSbDir As Boolean)

Dim flDir As String
Dim CldItm As Variant
Dim sCldItm As New Collection

' Add backslash at the end of path if not present
pPath = Trim(pPath)

If Right(pPath, 1) <> "" Then pPath = pPath & ""

' Searching files accordant with mask
flDir = Dir(pPath & pMask)
    Do While flDir <> ""
        pFnd.Add pPath & flDir 'add file name to list(collection)
        flDir = Dir ' next file
    Loop

' Procedure exiting if searching in subdirectories isn't enabled
If Not pSbDir Then Exit Sub

' Searching for subdirectories in path
flDir = Dir(pPath & "*", vbDirectory)
    Do While flDir <> ""
    ' Do not search Scheduling folder
        If flDir <> "Scheduling" Then
            ' Add subdirectory to local list(collection) of subdirectories in path
            If flDir <> "." And flDir <> ".." Then If ((GetAttr(pPath & flDir) And _
            vbDirectory) = 16) Then sCldItm.Add pPath & flDir
        End If
        flDir = Dir 'next file
    Loop

' Subdirectories list(collection) processing
For Each CldItm In sCldItm
    Call FlSrch(pFnd, CStr(CldItm), pMask, pSbDir) ' Recursive procedure call
Next

End Sub


Hi I have a couple of problems trying to do calculations on a user form with Excel 2000 I am used to using VBA with Access 2000.

I have created a data worksheet to store the data that I need to keep called (Database) and another to store lists for combo boxes and list boxes called (lists) and another to store constant values that once entered will not be changed and are used to preform calculations called (constants) the Database worksheet has formula and formatting in the cells that preform what I wont it to do. This all seams to work ok.
The user form is to make data entry for the user simple and easy! And the worksheets do everything else!
The problems I am having is on the user form I have a default start time test box and a default finish time text box that when the form is opened it looks at the constants work sheet and if there is any data it puts it into the appropriate text box, all ok so far when a user wishes to change the default data in these text boxes I need to test if they are entering the data correctly, in Access 2000 I would set the defeat format of the text box to Time? Data type=Format(time,”h:mm”) or a or an input mask of “h:mm” so if they did not enter the time in the correct way an error message would pop up and tell them to renter the data the correct way.
I have not been able to find this type of settings in Excel 2000 I have tried testing the data using the beforupdate but it also dos not recognise formatting

The other thing I need to do on the user form with this formatting is calculate the time difference between to two time inputs and come up with a total hours.
I have managed to do all of this in the work sheets and preforms well but not on the user form!
I know that there are differences with VBA for Access and VB for Excel but they are simular!
Can any one offer any help with this?


No luck finding an answer? You could always try Google.