How to force cells to return n/a if left blank?

ebracky · Oct 15th 2017

I think this is the same code you suggested before, the one that is giving me the "Run-time error 13: Type mismatch". I'm only able to fix it if I remove the "#" in "#N/A" and just use "N/A" instead. Which is ok for me but it shows "N/a" (with small "a") in cell C3 instead of the normal "N/A" in the other blank cells. Thanks

**Carim** · Oct 16th 2017

Hello,

I should have explained more clearly the modification :

Code

Private Sub Worksheet_Change(ByVal Target As Range)


''''''''''''''''''''''''''''''''''''''''''''
'Forces text to PROPER case for the range C3
''''''''''''''''''''''''''''''''''''''''''''


If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub


    On Error Resume Next
    If Not Intersect(Target, Range("C3")) Is Nothing Then
      Application.EnableEvents = False
      '    Modification added to prevent interference with the Copy Macro  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
       If Target.Value <> "N/A" Then
          Target = StrConv(Target, vbProperCase)
      End If
      Application.EnableEvents = True
    End If
    On Error GoTo 0


''''''''''''''''''''''''''''''''''''''''''''
'Forces text to UPPER case for the range C4 & E4
''''''''''''''''''''''''''''''''''''''''''''


If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub


    On Error Resume Next
    If Not Intersect(Target, Range("C4,E4")) Is Nothing Then
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
    End If
    On Error GoTo 0


    If Not Intersect(Range("E4"), Target) Is Nothing Then
        ActiveSheet.CallReasons.Activate
    End If


End Sub

Display More

Hope this explanation is clearer ...

ebracky · Oct 17th 2017

OK I get it now, yes I just tried it again and it's already working. Thank you very much. Will try to work on the other features listed on my previous post. Thanks I really appreciate your assistance on this.

**Carim** · Oct 17th 2017

Quote from ebracky

OK I get it now, yes I just tried it again and it's already working. Thank you very much. Will try to work on the other features listed on my previous post. Thanks I really appreciate your assistance on this.

Glad you could fix your problem .. :wink:

Thanks ... for your Thanks ... :smile:

ebracky · Oct 22nd 2017

Hello,

I'm back again. I'm trying to fix one of the features I'm working on this workbook. The tabbing In and Out of ComBoxes to make documentation much easier for the users. Right now the code below only works if I edit Cell E4 (Auth/Case#). If I skip and not edit E4, I'm able to Tab to ComboBox1 (Call Reason) but the ComboBox is not activated. The ComboBox is not activated if I skip it. I want to be able to Tab or hit Enter key from either Cells C4 (AMI#) or Cell E4 (Auth/Case#), with or without editing them and have the next ComboBox activated. I hope to also be able to tab in/enter to the ComboBox2 (Call Resolution).

This is the code I have right now:

If Not Intersect(Range("E4"), Target) Is Nothing Then
ActiveSheet.CallReasons.Activate
End If

**Carim** · Oct 23rd 2017

Hello,

In order to help you out ... could you please attach your latest updated file ... in order to better visualize the problem ...

ebracky · Oct 24th 2017

Ok here is the latest file I have for this workbook. Thanks!

**Carim** · Oct 26th 2017

Hello,

Sorry .... but password protection ... is preventing from testing any possible improvements ... :roll:

ebracky · Oct 29th 2017

Sorry for that. Here's the workbook again. Thanks

**Carim** · Oct 30th 2017

Hello,

Thanks for your password free workbook ... :wink:

Will take a look at your earlier remarks ... and your objective ...

ebracky · Nov 4th 2017

Thanks I really appreciate it!

How to force cells to return n/a if left blank?

Participate now!

Share

Users Viewing This Thread