CODEWITHSUNDEEP
vbaexcelexcel-2007
ummecasino
ummecasino

Reputation: 347

Validate entered cell value according to a given data type

I use an Excel macro to create new workbooks filled with data of given csv files. One column is open to user input of certain attribute values. These values must fit a datatype and a certain formatting to be accepted by another processing system, here are some examples:

Now i want to add a validation to check if a typed input value is ok for processing or has to be changed, examples:

How do I set up this validation? I am quite new to VBA not shure about the best way to go. The possibilities I found so far:

  1. Add a validation via Worksheet.Range.Validation, but I don't have any clue how to set up the Formula1 to check for correct values.

  2. Go by Worksheet_Change, the problem here is that i create new Workbooks which apparently not contain the Worksheet_Change I wrote in my program code because it is a new workbook.

So what's the best way to go?

Upvotes: 1

Views: 1445

Answers (2)

Wolfgang Kuehn
Wolfgang Kuehn

Reputation: 12926

Using Worksheet.Range.Validation is not very save because it is not triggered if user pastes data from clipboard.

Go for Workbook_SheetChange. Implement the validation there and save the workbook as your template.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Debug.Assert (Format(Target.Value, "#.##0,0#") = Target.Value)
End Sub

Make sure all cells are formatted as text if you use this approch. Otherwise Target.Value will give you an interpolated value.

Then later open the template and import the cvs into it:

wb = Application.Workbooks.Add("path-to-template.xslm")
Application.EnableEvents = false ' You don't want to trigger change events during import
myimport(wb)
Application.EnableEvents = true

Upvotes: 0

Aaron Thomas
Aaron Thomas

Reputation: 5281

You can do quite a bit with data validation on the workbook side, without VBA. For example, for Value C, you could put in the formatting (Float value formatted "#.##0,0###") in the custom number format for that cell, then apply data validation to make sure only numbers are entered in a range you'd like.

Upvotes: 1

Related Questions