1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Stop A* being entered

Discussion in 'Business Applications' started by AD_Taylor, Jan 25, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. AD_Taylor

    AD_Taylor Thread Starter

    Joined:
    Jan 16, 2010
    Messages:
    93
    I am working with an Excel Spreadsheet (2007) that stores assessment grades for the students in my school. At the moment, teachers go on and access the spreadsheet from a shared drive, add their changes and then save. The problem is that some teachers are entering 'A*' which Excel recognises as 'A' plus any other characters due to ‘*’ being the wild card character. I have Data Validation on the cells in question to only allow them to enter grades such as ‘A+’ and ‘A’ but even with this on the ‘A*’ can still get through. I have quite a few calculations and comparison formulas on this sheet that require ‘A*’ to be entered as ‘A+’ in order to be calculated. Is there any way to stop them entering these grades in the first place? Otherwise I could settle with a way to replace them easily, as the Replace function also recognises ‘A*’ as the wild card character and replaces the letter A anywhere it appears on the sheet?
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You can use the Worksheet Change event to intercept when someone enters the the value in the cells and if the "*" is present replace it with a "+" or remove it all together.
    What Column is the A going to be entered in?
     
  3. AD_Taylor

    AD_Taylor Thread Starter

    Joined:
    Jan 16, 2010
    Messages:
    93
    Thanks for replying. There are actually around 5-10 columns that the grades will be entered into so I needed it to check all of them really. As usual though I think I've solved it myself! See code below:
    Code:
     'Put this bit in ThisWorkbook under Microsoft Excel Objects in VBA editor 
    Private Sub Workbook_BeforeClose(Cancel As Boolean) 
     
    Call ConvertAStar 
     
    End Sub 
    
    'Put this bit into a Module within the workbook
    Sub ConvertAStar()
    
    'Current Cell variable
    Dim currCell As Range
    
    'For each cell in the range specified
    For each currCell in Range("J17:O500") 
    
    'If the current cell value is A* replace with A+
    If currCell = "A*" Then 
    
    currCell = "A+"
    
    End If 
    
    'Check next cell in range
    Next currCell
    
    End Sub 
    After a bit of testing what this does is let the user enter the 'A*' grades but then when they close just before they save any A* grades entered in the range 'J17:O500' (the range they can enter grades into) are converted to 'A+'

    Thanks for the reply though!
     
  4. JohnWilson

    JohnWilson

    Joined:
    Nov 22, 2007
    Messages:
    262
    Glad its solved but wouldn't this one liner work?
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If UCase(Target) = "A*" Then Target = "A+"
    End Sub
     
  5. AD_Taylor

    AD_Taylor Thread Starter

    Joined:
    Jan 16, 2010
    Messages:
    93
    @JohnWilson

    Thanks for the code, as I'm still getting used to Public Subs and what they can do so didn't know about the Worksheet_Change event. However, the code doesn't seem to work when I am using it. I have copied it in as it is onto the ThisWorkbook part of the VBA project, and when an 'A*' is entered it just stays there? As far as I can see there is no visible change to anything on the sheet?

    Any ideas?
     
  6. AD_Taylor

    AD_Taylor Thread Starter

    Joined:
    Jan 16, 2010
    Messages:
    93
    Sorry my fault was putting the code in the wrong place! Should have known that a 'Worksheet' event needs to go on the code of the actual sheet the change is taking place, not the ThisWorkbook part

    Thanks for this works perfectly and I'll use this in future.
     
  7. JohnWilson

    JohnWilson

    Joined:
    Nov 22, 2007
    Messages:
    262
    To use in WorkBook try
    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If UCase(Target) = "A*" Then Target = "A+"
    End Sub
     
  8. AD_Taylor

    AD_Taylor Thread Starter

    Joined:
    Jan 16, 2010
    Messages:
    93
    @JohnWilson

    Thanks for your help with this, however, I have found an error with the code. When making a change to multiple cells at a time, e.g. Highlighting 2 cells and deleting their contents or using auto fill down a column, I get the error

    Run Time Error '13'

    Type mismatch

    Found that the original code was looking for a single cell range to use for the Target variable, and when selecting multiple cells was getting obviously a larger range. I've included a temporary range variable so that if a multiple range is selected the macro will check each individual cell in the range rather than trying to check it all at once. See code below:

    Code:
    'Entered into the sheet the change is going to be made in
    'Activates when any data is changed on the sheet; if the change modifies a cell to A* this macro will replace it with A+
    
      Private Sub Worksheet_Change(ByVal Target As Range)
    
      Dim tempRng As Range
    
      For Each tempRng In Target
    
          If UCase(tempRng) = "A*" Then
    
              tempRng = "A+"
    
          End If
    
    Next tempRng
    
      End Sub
    
    
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/976843

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice