List Data Validation and Green Triangle

[MikeMcCollister]

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]

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.

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

 

[Peter_SSs]

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]

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]

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:

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

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

Regards,

Mike

 

[MikeMcCollister]

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]

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

Sub ErrorCheckOff()
  Application.ErrorCheckingOptions.ListDataValidation = False
End Sub

In the particular worksheet's module

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)

Private Sub Workbook_Open()
  If ActiveSheet.Name = "DV" Then ErrorCheckOff
End Sub

 

[MikeMcCollister]

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

Sub ErrorCheckOff()
  Application.ErrorCheckingOptions.ListDataValidation = False
End Sub

In the particular worksheet's module

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)

Private Sub Workbook_Open()
  If ActiveSheet.Name = "DV" Then ErrorCheckOff
End Sub

Peter,

Thanks. That works better.

Regards,

Mike

 

[Peter_SSs]

You're welcome. Glad we could help.

 

[MikeMcCollister]

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.

'*************
'** 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