+ Reply to Thread
Results 1 to 7 of 7

Allowing macro to run while typing words into cells

Hybrid View

  1. #1
    Registered User
    Join Date
    11-02-2006
    Posts
    47

    Allowing macro to run while typing words into cells

    Is it possible to have a macro in a row of cells and then type words into one of those cells without having the macro stop? When I type in a cell that contains a macro, it gives me a run time error.

    I've attached a sample file of what I'm trying to do. On the sheet titled "Day1", row 6 has a macro. If I were to type "out" or "dog" for example, in one of the cells, what do I need to change in order to have the macro keep running. I've protected the work sheet and locked all the cells that I don't want used, but I'm not sure how to get past this problem. Thanks for the help.

    Cellar Webs
    Attached Files Attached Files

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Your cells do n't contain macros, you have code in the Worksheet object. What are you expecting, I can type a value into the specified rows & the calculation runs no problem.

    This is a tidier way to write your code

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 11 Then Exit Sub
        Select Case Target.Row
            Case 6, 7, 18, 4, 38, 42    '<<<< change cell to suit needs
                Application.EnableEvents = False
                Target.Value = Target.Value * 0.99
            Case Else: Exit Sub
        End Select
        Application.EnableEvents = True
    End Sub
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    11-02-2006
    Posts
    47
    Thanks for the reply. I already know that the cells will do the calculation but say I typed a word into that cell. Well, it says "Run-time error '13': Types mismatch." Is there a way that I can still have the code work even though I might type a word into that cell instead of a number?

    Cellar Webs

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You can either use Data validation to only allow numbers or add an On Error statement

    On Error Resume Next
    'the code
    On Error GoTo 0

  5. #5
    Registered User
    Join Date
    11-02-2006
    Posts
    47
    Where would I paste this code? Thanks for the help.

    Cellar Webs

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Like this

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 11 Then Exit Sub
    On Error Resume Next
        Select Case Target.Row
            Case 6, 7, 18, 4, 38, 42    '<<<< change cell to suit needs
                Application.EnableEvents = False
                Target.Value = Target.Value * 0.99
            Case Else: Exit Sub
        End Select
        Application.EnableEvents = True
    On Error GoTo 0
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1