List Data Validation and Green Triangle

MikeMcCollister

New Member
Joined
May 6, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
In my budget spreadsheet I use list data validation in my "type" column. The list options are as follows:

ATM
Deposit
EFT

This is a convenience to allow me to have a pulldown for the options. In the data validation I have the error alert disabled as I also want to allow for check numbers, like 1234 or 3456. This works fine but if a check number is added then a green triangle is visible in the upper left of the cell. I know that I can turn this off in Excel, but this setting is for all spreadsheets in Excel, not only the worksheet or workbook that I want these green triangles to go away.

Is there a way to have a data validation that allows for a pulldown for convenience but still allows for numbers without being considered invalid data?

Thanks,

Mike
 

MikeMcCollister

New Member
Joined
May 6, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Yes, my suggestion was for my guess of the message being 'Number stored as text', not for a 'data validation error'.

If you only want these particular triangles disabled for this particular workbook, then I think you will have to use vba, but then any user of the workbook will need to have macros enabled for it to work. If you want to test, put this in the ThisWorkbook module of the workbook in question then close & reopen or move to another workbook and back.

VBA Code:
Private Sub Workbook_Activate()
  Application.ErrorCheckingOptions.ListDataValidation = False
End Sub

Private Sub Workbook_Deactivate()
  Application.ErrorCheckingOptions.ListDataValidation = True
End Sub
Peter,

Thanks. That code almost worked, I had to change it to Worksheet_* instead of Workbook_*.

Regards,

Mike
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
I had to change it to Worksheet_* instead of Workbook_*.
Is the code still in the ThisWorkbook module?
Does the workbook have more than one worksheet?

Can you post the full modified code?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,111
Office Version
  1. 365
Platform
  1. Windows
I can reproduce that error (yes, in a structured table which is what it appears to be in the post 6 image)
I was hovering the mouse over the ! rather than clicking the drop down.
 

MikeMcCollister

New Member
Joined
May 6, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Is the code still in the ThisWorkbook module?
Does the workbook have more than one worksheet?

Can you post the full modified code?
Peter,

I did not use the ThisWorkbook module. I wanted it only for a specific sheet so I put it in that module.

Here is the modified code:

VBA Code:
Private Sub Worksheet_Activate()
    Application.ErrorCheckingOptions.ListDataValidation = False
End Sub

Private Sub Worksheet_Deactivate()
    Application.ErrorCheckingOptions.ListDataValidation = True
End Sub

Regards,

Mike
 

MikeMcCollister

New Member
Joined
May 6, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I was hovering the mouse over the ! rather than clicking the drop down.

I see that there is a different message with a hover. Interesting.

Mike
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
I wanted it only for a specific sheet so I put it in that module.

Here is the modified code:
Then I think that may not quite do what you want. If the particular sheet is the active sheet when you close the workbook, then when you re-open the workbook the Worksheet_Activate code on that sheet is not fired.

However, you could do this:

In a General module
VBA Code:
Sub ErrorCheckOff()
  Application.ErrorCheckingOptions.ListDataValidation = False
End Sub

In the particular worksheet's module
VBA Code:
Private Sub Worksheet_Activate()
    ErrorCheckOff
End Sub

Private Sub Worksheet_Deactivate()
    Application.ErrorCheckingOptions.ListDataValidation = True
End Sub

In the ThisWorkbook module (edit the sheet name to match yours)
VBA Code:
Private Sub Workbook_Open()
  If ActiveSheet.Name = "DV" Then ErrorCheckOff
End Sub
 

MikeMcCollister

New Member
Joined
May 6, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Then I think that may not quite do what you want. If the particular sheet is the active sheet when you close the workbook, then when you re-open the workbook the Worksheet_Activate code on that sheet is not fired.

However, you could do this:

In a General module
VBA Code:
Sub ErrorCheckOff()
  Application.ErrorCheckingOptions.ListDataValidation = False
End Sub

In the particular worksheet's module
VBA Code:
Private Sub Worksheet_Activate()
    ErrorCheckOff
End Sub

Private Sub Worksheet_Deactivate()
    Application.ErrorCheckingOptions.ListDataValidation = True
End Sub

In the ThisWorkbook module (edit the sheet name to match yours)
VBA Code:
Private Sub Workbook_Open()
  If ActiveSheet.Name = "DV" Then ErrorCheckOff
End Sub
Peter,

Thanks. That works better.

Regards,

Mike
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad we could help. :)
 

MikeMcCollister

New Member
Joined
May 6, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Peter,

I had another validation that I wanted to turn off and I went to the workbook way of doing things. Because I did not want to change any setting that I may have set, I saved the states of the validations before I change them.

Here is my new code.

VBA Code:
'*************
'** options **
'*************

Option Explicit

'******************************
'** private global variables **
'******************************
Dim gListDataValidation As Boolean
Dim gInconsistentTableFormula As Boolean

'*************************
'** private subroutines **
'*************************

Private Sub Workbook_Activate()
    With Application.ErrorCheckingOptions
        gListDataValidation = .ListDataValidation
        gInconsistentTableFormula = .InconsistentTableFormula
        
        .ListDataValidation = False
        .InconsistentTableFormula = False
    End With
    
End Sub

Private Sub Workbook_Deactivate()
    With Application.ErrorCheckingOptions
        .ListDataValidation = gListDataValidation
        .InconsistentTableFormula = gInconsistentTableFormula
    End With
End Sub


Regards,

Mike
 

Watch MrExcel Video

Forum statistics

Threads
1,113,978
Messages
5,545,312
Members
410,676
Latest member
M0J0jojo
Top