Macro starts on clicking only certain column cells

**msaz87** · 03-07-2008, 03:34 AM

Hi all,

I have it set up on a sheet for a macro to start whenever a cell is clicked, but I'm trying to figure out how to narrow that down to only certain column cells being clicked.

You can see the current code below... and it just basically activates the AddToCell macro once any cell is clicked. The problem with this is, it errors on cells with text in them and no longer lets me edit the sheet without disabling the macros and so on. So, instead of it activating on all cells, I'd like to find a way to have it just work for columns E-I.

Any help would be appreciated.

Thanks much

Sub AddToCell()
    'ActiveCell.Value places the value or formula into the active cell
    'on the active worksheet.
    '
    'ActiveCell.Value returns the value in the active cell of the active
    'window.
    '
    'InputBox calls up the Excel input box for you to type in the number
    'to be added to the original value. Val takes the text string
    'returned from the input box and turns it into a number.

    ActiveCell.Value = ActiveCell.Value + _
        Val(InputBox("Enter Stat", Default:=1))
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet5.AddToCell
End Sub

**royUK** · 03-07-2008, 03:55 AM

Firstly you need to move the macro to a Standard Module. Then add this code to your worksheet event, it will limit the action to the specified Columns & only empty cells

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column < 5 Or Target.Column > 9 Then Exit Sub
    If Not IsEmpty(Target) Then Exit Sub
    AddToCell
End Sub

**msaz87** · 03-07-2008, 04:01 AM

I'm an extreme novice when it comes to messing with VBA... can you explain to me a little more in depth how to do this with the module and everything?

Thanks for your help

**royUK** · 03-07-2008, 04:07 AM

Insert a Module in the VBA from yhe VB EDitor Insert menu. Cut the AddtoCell macro & paste it into the new module. Then overwrite the existing code in the sheet module with the new.

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'limit code to Columns E to I
    If Target.Column < 5 Or Target.Column > 9 Then Exit Sub
'Only work if cell has a value, you probably don't need this.
    If Not IsEmpty(Target) Then Exit Sub
'This might be better for you.
'It will limit the code to run on cells in the specified Columns that contain a Numeric Value
 If Not IsNumeric(Target) Then Exit Sub
'Calls your Macro
    AddToCell
End Sub

**msaz87** · 03-07-2008, 04:15 AM

OK.. almost there.. the only issue I've run into now is that the macro doesn't seem to work on pre-existing figures. The idea is that there is a number already in the cell and I can click it, type in a number and it will add to the previous one. It works on blank cells, but the ones that have something in them currently don't seem to run the macro.

Thanks for your help

**royUK** · 03-07-2008, 04:25 AM

In the notes I suggested that you might want to remove this line

'Only work if cell has a value, you probably don't need this.
    If Not IsEmpty(Target) Then Exit Sub

Like this:

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column < 5 Or Target.Column > 9 Then Exit Sub
'This might be better for you.
'It will limit the code to run on cells in the specified Columns that contain a Numeric Value
    If Not IsNumeric(Target) Then Exit Sub
    AddToCell
End Sub

Macro starts on clicking only certain column cells

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro starts on clicking only certain column cells

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions