#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
#3
|
||||
|
||||
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 |
|
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 |
Word - Resize image to specified selected cells' dimensions | FaizanRoshan | Word VBA | 7 | 10-18-2015 03:34 PM |
Code that will run when a cells value is changed. | DonJohns1 | Excel Programming | 2 | 04-15-2015 10:48 AM |
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 |