Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Take Action on Cells within Selection
Folks €“ I'm looking for a way for the end-user to select a sub-set, more often non-contiguous, of data within a worksheet to take action. I have a worksheet that will contain data in columns A through Y for any number of rows. My end-user will be instructed to ctrl+click on the row number of each row they want to take action. After which, the user will select a button that triggers a macro to hopefully execute on just the rows they've selected. One thought I have is to programmatically insert an "x" in column Z of each row in the selection. The logic being that each macro would only apply to rows with an "x" in column Z. Examples of actions are, find/replace values within the selected range, copy/paste selected rows into a new workbook and delete rows selected. All suggestions are greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Take Action on Cells within Selection
I'd create a range of the first column ("A") used in the selected rows. Then loop through that range. I could expand the single cell range by resizing it: Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next Set myRng = Application.InputBox(Prompt:="Select a range", Type:=8) On Error GoTo 0 If myRng Is Nothing Then MsgBox "Try later" 'user hit cancel Exit Sub End If Set myRng = Intersect(myRng.EntireRow, myRng.Parent.Columns(1)) MsgBox myRng.Address For Each myCell In myRng.Cells MsgBox myCell.Address & vbLf & myCell.Resize(1, 25).Address Next myCell End Sub Mike G - D.C. wrote: Folks €“ I'm looking for a way for the end-user to select a sub-set, more often non-contiguous, of data within a worksheet to take action. I have a worksheet that will contain data in columns A through Y for any number of rows. My end-user will be instructed to ctrl+click on the row number of each row they want to take action. After which, the user will select a button that triggers a macro to hopefully execute on just the rows they've selected. One thought I have is to programmatically insert an "x" in column Z of each row in the selection. The logic being that each macro would only apply to rows with an "x" in column Z. Examples of actions are, find/replace values within the selected range, copy/paste selected rows into a new workbook and delete rows selected. All suggestions are greatly appreciated. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Take Action on Cells within Selection
It is simpler than that... Record a macro (anything will do) in order to create a standard code module in the VBE. Go to the VBE and in the module add this code Public Sub DoStuff() Dim rng As Range For Each rng In Selection MsgBox rng.Address Next rng End Sub Now add a command button from the forms toolbar and connect it to this macro... -- HTH... Jim Thomlinson "Mike G - D.C." wrote: Folks €“ I'm looking for a way for the end-user to select a sub-set, more often non-contiguous, of data within a worksheet to take action. I have a worksheet that will contain data in columns A through Y for any number of rows. My end-user will be instructed to ctrl+click on the row number of each row they want to take action. After which, the user will select a button that triggers a macro to hopefully execute on just the rows they've selected. One thought I have is to programmatically insert an "x" in column Z of each row in the selection. The logic being that each macro would only apply to rows with an "x" in column Z. Examples of actions are, find/replace values within the selected range, copy/paste selected rows into a new workbook and delete rows selected. All suggestions are greatly appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Take Action on Cells within Selection
I really appreciate the suggestions and examples. This gives me something to
work with and try to understand. ----------------------------------------------------------------------------- Less Spam Better enjoyable experience Visit : news://spacesst.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
action upon selection | Excel Programming | |||
Action on sheet Selection | Excel Programming | |||
Action on sheet Selection | Excel Programming | |||
onClick action from cell selection | Excel Programming |