Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-19-2018, 12:57 AM
trevorc trevorc is offline run code only when certain cells selected Windows 7 32bit run code only when certain cells selected Office 2013
Competent Performer
run code only when certain cells selected
 
Join Date: Jan 2017
Posts: 148
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default run code only when certain cells selected

Hi All,
I'm trying to get the code below to run via a button click only when the active/selected cell is within a certain range. Currently that is between B2 and X5000, this is were I have customer data, and not if the cursor is anywere else on the sheet. I have tried - If Sheets("Clipsal Customer").Range.Selection("A4:Z5000") Then, but this throws up the error. Also tried to use If Not Intersect(Target, Sh.Range("A4:A5000")) Is Nothing Then... but can't get that to work either.
W
hat I need is code that first checks if the currently selected cell is within the desiered range then run the code.
Any help is appriciated.





Code:
 Sub button_click1()
        Sheets("Payment Advice").Range("B3") = Date
        Sheets("Payment Advice").Range("B4") = Range("a" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B5") = Range("g" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B6") = Range("h" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B7") = Range("i" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B8") = Range("j" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B9") = Range("k" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Select
End Sub
Reply With Quote
  #2  
Old 06-19-2018, 01:14 AM
trevorc trevorc is offline run code only when certain cells selected Windows 7 32bit run code only when certain cells selected Office 2013
Competent Performer
run code only when certain cells selected
 
Join Date: Jan 2017
Posts: 148
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default Got it

Found this on Stack overflow. Not sure why it won't display the code any better ?


Determine if a cell is within a range using VBA in Microsoft Excel:
From the linked site (maintaining credit to original submitter):
VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions
Code:
Function InRange(Range1 As Range, Range2 As Range) As Boolean    ' returns True if Range1 is within Range2    InRange = Not (Application.Intersect(Range1, Range2) Is Nothing)End FunctionSub TestInRange()    If InRange(ActiveCell, Range("A1:D100")) Then        ' code to handle that the active cell is within the right range        MsgBox "Active Cell In Range!"    Else        ' code to handle that the active cell is not within the right range        MsgBox "Active Cell NOT In Range!"    End If End Sub

Last edited by trevorc; 06-19-2018 at 01:16 AM. Reason: won't display the code very well.
Reply With Quote
  #3  
Old 06-19-2018, 02:40 AM
Debaser's Avatar
Debaser Debaser is offline run code only when certain cells selected Windows 7 64bit run code only when certain cells selected Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 172
Debaser is on a distinguished road
Default

FWIW, I'd write your code more like this:

Code:
Sub button_click1()
    Dim lRow As Long
    lRow = ActiveCell.Row
    If ActiveSheet Is Sheets("Clipsal Customer") Then
        If Not Intersect(ActiveCell, Sheets("Clipsal Customer").Range("A4:Z5000")) Is Nothing Then
            With Sheets("Payment Advice")
                .Range("B3") = Date
                .Range("B4") = Cells(lRow, "a").Value
                .Range("B5") = Cells(lRow, "g").Value
                .Range("B6") = Cells(lRow, "h").Value
                .Range("B7") = Cells(lRow, "i").Value
                .Range("B8") = Cells(lRow, "j").Value
                .Range("B9") = Cells(lRow, "k").Value
                .Select
            End With
        End If
    End If
End Sub
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
EXCEL 2016 - merging multiple selected cells kevin.suh Excel 2 03-28-2017 07:01 AM
run code only when certain cells selected Word - Resize image to specified selected cells' dimensions FaizanRoshan Word VBA 7 10-18-2015 03:34 PM
run code only when certain cells selected Code that will run when a cells value is changed. DonJohns1 Excel Programming 2 04-15-2015 10:48 AM
run code only when certain cells selected Auto repeat selected cells from tables into a summary AdieReeves Word Tables 7 10-22-2014 07:59 AM
How to keep orientation when printing Selected Cells punkrae Excel 1 04-09-2012 05:32 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 12:41 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft