Free Microsoft Excel 2013 Quick Reference

Cell entry validation Results

All,

The "growing" validation works fine if if it is on the same sheet. (See example sheet attached). You can either pick from the dropdown or add your own entry which makes the validation grow. Then you can use that grown list for validation.

But if you go to Sheet 2 and use those dropboxes (which get their data from a Sheet 1 validation list), the list doesn't grow and causes a code error. I thought if you used a named-list, you can use that anywhere without having to specify which sheet it is. Is that the problem with the code?

Private Sub Worksheet_Change(ByVal
Target As Range)

Dim lReply As Long
    If Target.Cells.Count > 1 Then Exit Sub
        If Target.Address = "$A$1" Or Target.Address = "$A$6" Then
            If IsEmpty(Target) Then Exit Sub
                If WorksheetFunction.CountIf(Range("MyColors"), Target) = 0 Then
                    lReply = MsgBox("Add " & Target & " to list?", vbYesNo + vbQuestion)
                        If lReply = vbYes Then
                        Sheet1.Unprotect Password:="excel"
                            Range("MyColors").Cells(Range("MyColors").Rows.Count + 1, 1) = Target
Sheet1.Protect Password:="excel"
                        End If
                End If
        End If
End Sub
??

VR/Lost

I have two columns on my spreadsheet that I want to edit together. One column has HST(column R) and one column has PST(column S). I do not want the people using this spreadsheet to be able to key in both R and S. They have to key in either R, S or neither but not both. How do I code Worksheet_change code to make this happen. I have seperate excel logic to highlight these two cells if both are keyed in. I found macro logic below on the web that will help me out to define the code that I want and I am in the process of modifying the code. How would I modify this code to get what I want. I want to produce an error if both fields are keyed in. I also want to change the fill colour if both fields are keyed in. Right now I have conditional formatting doing the fill colour. Here is the logic I found on the web that best suits what I am trying to do. How would I modify this code to do what I need to do to get it to work?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range, cell As Range
Dim Msg As String
Dim ValidateCode As Variant
Set VRange = Range("R:R:S:S")
For Each cell In Target
If Union(cell, VRange).Address = VRange.Address Then
ValidateCode = EntryIsValid(cell)
If ValidateCode = True Then
Exit Sub
Else
Debug.Print "Cell " & cell.Address(False, False) & ":" & ValidateCode
Application.EnableEvents = False
cell.ClearContents
cell.Activate
Application.EnableEvents = True
End If
End If
Next cell
End Sub
Function EntryIsValid(cell) As Variant
If cell = "" Then
EntryIsValid = True
Exit Function
End If

If Not IsNumeric(cell) Then
EntryIsValid = "Non-numeric entry."
Exit Function
End If

If CInt(cell) <> cell Then
EntryIsValid = "Integer required."
Exit Function
End If

If cell < 1 Or cell > 12 Then
EntryIsValid = "Valid values are between 1 and 12."
Exit Function
End If

EntryIsValid = True
End Function

Hi Guys,

I dont even know if this is possible (although i'm slowly realising that anything and everything is possible in excel!)

I would like to validate all info which is entered into a column.

Data is account codes.

I would like to use a userform to check the codes as they are entered.

something like:

user types code in cell -

Userform appears with entry box and ok button.

User re enters code in form.

form checks data is identical to data originally entered in cell.

If they are the same, the data is entered into the cell.

If they are different the userform prompts the user to re enter value in cell and process starts again.

any ideas if this is do-able?

thanks

Hi - New to VBA & Excel Forum.

I've created a "dummy" worksheet to simplify what I'm trying to do. I have a task list in A1:A5 and a data validation box for each task in B1:B5. The values are either "Complete" or "Not Complete". If the value is changed to "Complete", I would like to add the current date to the next cell over in C1:C5.

I've tried several different codes and ended up piecing this one together from something I found online -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim isect As Excel.Range
Set isect = Application.Intersect(Range(ActiveCell.Address), _
    Range("A1:A5"))
If isect Is Nothing Then
    'Do Nothing
Else
    Range("C1") = Now()
    'Code for entering today's date in the next cell over?
End If
End Sub
Two problems -

#1 - I can't seem to find a code that will put the date in the next cell over [i.e. Offset(0,1)] from the data validation box that was selected.

#2 - My biggest problem - When "complete" is selected, nothing happens until a value is physically typed in another cell within the A1:A5 range. Once entered, the "Now()" is entered into C1 based on the initial entry.

Hopefully this makes sense! I'm using Excel 2004 for Mac. Thank you ahead of time for your help!

Kevin

Hi All,
I've been struggling with this for a little while today and quite can't seem to figure it out. I have a range --- A6:A28 --- where a user will enter a percentage. The total should add up to a 100%, which is in cell A30.

My understanding is that a need a worksheet_calculate function to tell users to revise their entries if the results in a cell A30 are either less than or more than 100%.

This is the simple, non-working procedure I have now:

Private Sub
Worksheet_Calculate()
If Range("A30").Value > 1 Then _
MsgBox "A30 must equal 100%.  Please check your entries"
End Sub
Any ideas? I've attached the worksheet.

Thanks in advance for the help

Hi Experts,

I need to some help with a macro formula. I have Excel 2003 and have set up data validation rules to restrict entries users can make.

However, I noticed that users could still copy/paste over the cells with incorrect data that data validation tool did not pick up. I had a dig around on the net and found a bit of VBA code that could correct the issue. The VBA code is outlined below.

It works perfectly when the workbook is unshared but as soon as I share it the macro ceases to execute giving rise to the same issue as before. Unfortunately I require 20 users to acess the worksheet at various times so I must share it.

Can anyone suggest a way round this problem

Thanks,

Owen

Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("ValidationRange")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
End If
End Sub

Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

here is the code I'm trying to run. Hopefully the comments make it clear what I'm trying to do. There are multiple errors:
1. the named range "output_response_sheet_dv_list" is not being created in the workbook
2. the For Each/Next loop is erring when I try to populate those cells

    'define the VBA range
"output_sheet_range"
    With ActiveWorkbook.Sheets("hidden_named_ranges")
        Set output_sheet_range = .Range(.Cells(2, 3), .Cells(num_design_sheets + 1, 3))
    End With
    'add a named range ("output_response_sheet_dv_list") to the worksheet that corresponds to the VBA range
("output_sheet_range")
    ActiveWorkbook.Sheets("hidden_named_ranges").Names.Add Name:="output_response_sheet_dv_list",
RefersTo:="=output_sheet_range", Visible:=True
    'fill "output_response_sheet_dv_list" with allowed sheet names
    Dim c As Range
    For Each c In Worksheets("hidden_named_ranges").Range("output_sheet_range").Cells
        c.Value = allowed_sheet_name(row)
    Next

    'delete and redefine data validation to accept only new named range entries for allowed sheets
    For row = 2 To 16
        Range("output_response_sheet").Cells(row).Validation.Delete
        Range("output_response_sheet").Cells(row).Validation.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:= _
                                                                 xlBetween,
Formula1:="=output_response_sheet_dv_list"
    Next row


Greetings all

I am a new user to Excel and am currently trying to write an error-handling subroutine for a message-box to test for a Vaild entry in cell BD3 based on a range of cells at a101:a1000. The range is numeric and ascending order but most of the cells that follow the last entry are blank. There are probably 20 valid entries so far.

If the test cell is blank or outside of the range, the subroutine would invoke the msgbox with something like "In-valid entry; Retry or Cancel"

On retry, a valid entry is made then the error subroutine exits, allowing the rest of the macro to continue. Cancel of course, will simply exit the subroutine with no further action.

Does anybody have any ideas on how this can be done?

Your help is appreciated

Is there a method whereby I can lock a data validation list if certain cells have a value >0. In accomplishing this I still need the drop down list available for rows that remain blank.

I.E. Column J has a data validation list with three items. Based upon the selection in the drop down I have sent data to three tabs. I have eliminated double entries and blank rows on each tab. Data is then entered on the selected tabs by tab. The system works great; however, if someone (and I know they will) changes one of three choices on the drop down list when data has been entered on the tabs the data on the tab that is prefilled based upon the drop down shifts up (to fill the blank) making the data entered on just the tabs appear on the wrong row.

I have created a user form that has a text box asking the user to enter an
employee ID number. Upon entry, the number is placed in cell C3 on the
spreadsheet. There is a VLOOKUP formula in cell C2 that goes to another
sheet (in the same workbook) and searches for the ID number and brings back
to cell C2 the cooresponding employee name.

If the number entered in the text box is not found then #N/A results in cell
C3.
I want to create a msgBox that would pop up as soon as an invalid number is
entered in the text box saying "Invalid Employee ID Number".

How do I get there?

My TextBox code is as follows:

Private Sub TxtEmployeeNumber_Change()
Range ("C3")=TxtEmployeeNumber.Text
End Sub

I have a cell that is populated through a formula, I was hoping to have an
error to display if the formula brings that cell below zero. I would also
like to cancel the event that lead up to this error.

Any ideas?

Please help, I am new to vba.....
I have an excel worksheet with list validation in cell j10. When a user chooses an option on the list I want to lock/unlock specific cells for entry. My problem is that choosing an option in the validation list is not triggering the worksheet change event. I tried changing the even to selection change but this didn't work either. I would appreciate any help or suggestions!!!!

Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "j10" Then
If Range("J10").Value = Range("M26").Value Then
ActiveSheet.Unprotect
Range("J12,J16,J18,J19,J20,J22,J24,J26,J28").Activate
Selection.Locked = True
Range("J16,J18,J20,J22,J24,J26,J28").Activate
Selection.Locked = False
ActiveSheet.Protect
ActiveSheet.EnableSelection = xlUnlockedCells
ElseIf Range("J10").Value = Range("M27").Value Then
ActiveSheet.Unprotect
Range("J12,J16,J18,J19,J20,J22,J24,J26,J28").Activate
Selection.Locked = True
Range("J16,J18,J20,J22,J24,J26,J28").Activate
Selection.Locked = False
ActiveSheet.Protect
ActiveSheet.EnableSelection = xlUnlockedCells
ElseIf Range("J10").Value = Range("M28").Value Then
ActiveSheet.Unprotect
Range("J12,J16,J18,J19,J20,J22,J24,J26,J28").Activate
Selection.Locked = True
Range("J16,J18").Activate
Selection.Locked = False
ActiveSheet.Protect
ActiveSheet.EnableSelection = xlUnlockedCells
ElseIf Range("J10").Value = Range("M29").Value Then
ActiveSheet.Unprotect
Range("J12,J16,J18,J19,J20,J22,J24,J26,J28").Activate
Selection.Locked = True
Range("J12").Activate
Selection.Locked = False
ActiveSheet.Protect
ActiveSheet.EnableSelection = xlUnlockedCells
ElseIf Range("J10").Value = Range("M30").Value Then
ActiveSheet.Unprotect
Range("J12,J16,J18,J19,J20,J22,J24,J26,J28").Activate
Selection.Locked = True
Range("J16,J18,J19,J20,J22").Activate
Selection.Locked = False
ActiveSheet.Protect
ActiveSheet.EnableSelection = xlUnlockedCells
Else
ActiveSheet.Unprotect
Range("J12,J16,J18,J19,J20,J22,J24,J26,J28").Activate
Selection.Locked = True
ActiveSheet.Protect
ActiveSheet.EnableSelection = xlUnlockedCells
End If
End If
End Sub

Hello...

I am trying to create a form spreadsheet with many
different formulas and validations...

My stumping point is the following:

In Cells E43:E60 I have dropdown lists (each cell contains
the same list).

Cells E43:E60 - content: Validation - Dropdown list
(exact same list in all of these cells)

Cells N43:N60 - content: User entry cell for Qty.
Cells P43:P60 - content: Units
Cells S43:S60 - content: My problem (see below)
Cells Z43:Z60 - content: Formula for total cost

My problem:

I need a validation in the "S" cells to do the following:
When an item is selected in the dropdown list in E43 - I
need S43 to be less than or equal to a $$ amount.
Everything in the dropdown list has different MAX
allowable amounts - so to just enter one amount is not
going to work. I've been playing with the validation
formulas - but can't seem to get any to work in the entire
range needed.

TIA,
TC

Excel 2003
Hi, I'm having problems with delete/clear behavior in a time sheet
application that I'm working on and I hope someone will help.

I have a range named TimeEntry that contains 14 columns with 6 rows each:
Row[1] a time-In
Row[2] a time-Out
Row[3] a time-In
Row[4] a time-Out
Row[5] a time-In
Row[6] a time-Out
followed by a total row which contains the formula
=((Row2-Row1)+(Row3-Row4)+(Row5-Row6))*24

I validate user entries in the WorksheetChange handler using the code that
follows.
The sheet is protected.

The problem is that when a user clears an entry that unbalances an In/Out
pair of cells the total becomes invalid. I do want the user to be able to
clear the entire TimeEntry range, an entire column, and individual In/Out
pairs in the column and also to be able to paste valid entries.

Bonus behavior would allow the user to enter In and Out times in arbitrary
places in the column for instance:
a time In in Row[1] and a time Out in Row[6]
or,
a time In in Row[3] and a time Out in Row[4] and a time In in Row[5] and a
time Out in Row[6]
etc.

If I haven't made my request for help clear or haven't provided enough info
please ask.
Thanks in advance for any help.

Jeff Higgins

Code follows:

'Catch Range array events. i.e. Cut, Copy, Delete, Clear etc.
If VarType(Target.Value) > vbUserDefinedType Then
Exit Sub
End If

'Does not attempt to validate changes outside the range named "Validate".
If Intersect(Target, Range("Validate")) Is Nothing Then
Exit Sub
End If

If Not Intersect(Target, Range("TimeEntry")) Is Nothing Then
If Not VarType(Target.Value) = vbDouble Then
Workbooks("Timesheet.xls").Application.EnableEvents = False
Target.Select
Target.Value = ""
Workbooks("Timesheet.xls").Application.EnableEvents = True
Exit Sub
End If

If Target.Value < 0 Or Target.Value > 2400 Then
Workbooks("Timesheet.xls").Application.EnableEvents = False
Target.Select
Target.Value = ""
Workbooks("Timesheet.xls").Application.EnableEvents = True
MsgBox "Please enter time as a number between 0 and 2400" &
Chr(13) & Chr(13) & _
"For example:" & Chr(13) & "enter midnight as 0. Displays as 0:00" &
Chr(13) & _
"enter noon as 1200. Displays as 12:00" & Chr(13) & "enter 5 PM as 1700.
Displays as 17:00"
Exit Sub
End If

If Target.Value Mod 100 > 59 Then
Workbooks("Timesheet.xls").Application.EnableEvents = False
Target.Select
Target.Value = ""
Workbooks("Timesheet.xls").Application.EnableEvents = True
MsgBox "Please enter time in 24hour/60minute format."
Exit Sub
End If

If Intersect(Target, Range("TimeEntryFirst")) Is Nothing Then
If Target.Offset(-1, 0) = Empty Or Target.Offset(-1, 0).Value *
24 * 100 > Target.Value Then
Workbooks("Timesheet.xls").Application.EnableEvents = False
Target.Select
Target.Value = ""
Workbooks("Timesheet.xls").Application.EnableEvents = True
MsgBox "Please enter an ""OUT"" time later than the ""IN""
time above."
Exit Sub
End If
End If
'Change "24hour/60minute" entry format to HH:MM display format.
Workbooks("Timesheet.xls").Application.EnableEvents = False
Target.Value = Format(Target.Value, "00:00")
Workbooks("Timesheet.xls").Application.EnableEvents = True
Exit Sub
End If

hello again!

i'd just like to ask if there is a work-around on the "List" in the Validation Criteria tab in the Data>Validation menu.

is it possible to have the list in another sheet?

i'm trying to limit data entry to cells via a drop-down list based on this.

if there is a different and much easier way of doing so i'd really appreciate the help. just simple, basic stuff though since i'm not that savvy on macros and all.

thanks in advance.

Hello! Friends

I have a list of names containing duplicate names and empty cells.

Using Data > Validation > List

I want get a drop down list of unique entries having no empty cells.

How can do this??? Please help me...

I have a list of companies on a sheet named Customers. (A2:A600) There are many blank cells due to the subsequent columns having multiple contacts for each company.

On another sheet I need to set up a dropdown list referencing the list on Customers. How do I get the list to show only the cells with entries--not the blanks using validation?

Does anyone have a formula

Mike

Hello Everyone!

I've been tasked with assigning some data validation to a worksheet, and I need the following things to happen:Phone number cells need to accept only 10-digit numerical entries, and the boss wants the cell to turn red if an invalid number is entered.Email cells need to turn red if the entry doesn't end in ".com".
I can do all of that (I think), but the problem is that this sheet is designed specifically to have data copied into it. If the data is copied form another sheet, pasting special values will keep the conditional formatting/data validation in place, but unfortunately I can't count on end users to use paste special values

So, is there a way to "lock" the conditional formatting of a cell even if another cell with different formatting is copy/pasted into it?

Thanks!
RA

In column J:J I am using a validated list of what can be entered. From this I have a VBA that sends the row to a sheet of the same name as the entry in the J cell (Thanks FoxGuy!)

What I would like to do do is have the sheet automatically be created if the sheet doesn't already exist. If it does already exist, nothing will happen.

For example I put Mar13 in cell J2 - I know this is a correct entry due to validation - But the sheet does not yet exist. What do I need to do to be able to create the sheet automatically? The sheet would be a copy of 'Mar10' currently in the (attached) work book. The name of the sheet will be the new date (Mar13 in the above example)

I have attached the current work book. I would presume the task would run at the same time as the current VBA runs.

Thanks.

Rob

I have 3 requirements:

1) The rows should be sorted ascending based on col E, then F. Some entries in col E are integers and some are integers with dash at the end, like "6548-", "127381-". Col F can be an integer or empty. I need to be able to sort values in col E so that it will ignore the dash and consider only the numbers that come before it. For example,
E          F
------------
100        2
10-        3
10-        
2000       1
123-       5
This should sort as:
E          F
------------
10-        
10-        3
100        2
123-       5
2000       1

2) The order (based on columns E and F as described above) should remain as new rows get added. For instance, if I add a row (or record), it should either prompt me if I put it in the correct row such that the sort order remains OR excel can sort the rows automatically, probably after saving or after the values for cols E and F are entered.

3) I have date header columns (say Oct 1, Oct 2, Oct 3, etc). The value of the row under Oct 1 should be less than or equal to that of Oct 2.. and the value under Oct 2 should be less than or equal to that of Oct 3. That is, as the date progresses, the value increases (or remains the same) - not less than the value of the day before it. The complexity here is the cell can be empty or can be text. But if it is a number, then the comparison should be done between the previous day which has a numeric value. For instance, Oct 1 has 200. For Oct 2, the cell is empty so no validation is needed. For Oct 3, the cell has text "off", so again no validation is needed. For Oct 4, the cell value is 119. Then the system should invalidate this entry since it is smaller than 200 (Oct 1 entry) which is the latest previous numeric entry.

My biggest difficulty is the 3rd requirement. However, if you can help me with all - that'll be great. Thanks!