| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Active range/selection?
Hi,
I would like to have a macro which picks up my selected range in a workbook, similair to the activecell. So when I select a range in my workbook, it will be set as range in my macro. I've tried active.range but this doens't work (something like this: Set dd = Active.Range). I can't find it in the list properties/methods in vba. Could somebody help me? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Active range/selection?
On 15 Sep 2006 04:46:27 -0700, "anita" wrote:
Hi, I would like to have a macro which picks up my selected range in a workbook, similair to the activecell. So when I select a range in my workbook, it will be set as range in my macro. I've tried active.range but this doens't work (something like this: Set dd = Active.Range). I can't find it in the list properties/methods in vba. Could somebody help me? Thanks in advance. If your range is a contiguous range with no columns or rows with null values, then with the active cell somewhere in the range you could use Set dd = ActiveCell.CurrentRegion HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Active range/selection?
Hi Anita
I think you need something like set dd - activecell.currentregion -- Regards Roger Govier "anita" wrote in message ps.com... Hi, I would like to have a macro which picks up my selected range in a workbook, similair to the activecell. So when I select a range in my workbook, it will be set as range in my macro. I've tried active.range but this doens't work (something like this: Set dd = Active.Range). I can't find it in the list properties/methods in vba. Could somebody help me? Thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Active range/selection?
Sorry
That "-" was meant to be an "=" set dd = activecell.currentregion -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Anita I think you need something like set dd - activecell.currentregion -- Regards Roger Govier "anita" wrote in message ps.com... Hi, I would like to have a macro which picks up my selected range in a workbook, similair to the activecell. So when I select a range in my workbook, it will be set as range in my macro. I've tried active.range but this doens't work (something like this: Set dd = Active.Range). I can't find it in the list properties/methods in vba. Could somebody help me? Thanks in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Active range/selection?
Hi Roger and Richard,
Thank you both for the quick respons. In my macro it doens't really work as I hoped it to be. When I run this macro he only activates the first cell and fills in a 1 if it has the beneath conditions. Maybe I'm doing something wrong. Below is my macro: Sub oke() Application.ScreenUpdating = False ActiveSheet.Unprotect ("arnhem12") Set d = ActiveCell.CurrentRegion For Each c In d If c.Interior.ColorIndex = xlNone And _ c.Borders(xlEdgeLeft).Weight = xlThin And _ c.Borders(xlEdgeLeft).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeTop).LineStyle = xlContinuous And _ c.Borders(xlEdgeTop).Weight = xlThin And _ c.Borders(xlEdgeTop).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeBottom).LineStyle = xlContinuous And _ c.Borders(xlEdgeBottom).Weight = xlThin And _ c.Borders(xlEdgeBottom).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeRight).LineStyle = xlContinuous And _ c.Borders(xlEdgeRight).Weight = xlThin And _ c.Locked = False And _ c.Borders(xlEdgeRight).ColorIndex = xlAutomatic Then c.Value = 1 End If Next c ActiveSheet.Protect ("arnhem12") MsgBox ("Finished") Application.ScreenUpdating = True End Sub Greetings anita Roger Govier schreef: Sorry That "-" was meant to be an "=" set dd = activecell.currentregion -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Anita I think you need something like set dd - activecell.currentregion -- Regards Roger Govier "anita" wrote in message ps.com... Hi, I would like to have a macro which picks up my selected range in a workbook, similair to the activecell. So when I select a range in my workbook, it will be set as range in my macro. I've tried active.range but this doens't work (something like this: Set dd = Active.Range). I can't find it in the list properties/methods in vba. Could somebody help me? Thanks in advance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Active range/selection?
Hi Roger and Richard,
Thank you both for the quick respons. In my macro it doens't really work as I hoped it to be. When I run this macro he only activates the first cell and fills in a 1 if it has the beneath conditions. Maybe I'm doing something wrong. Below is my macro: Sub oke() Application.ScreenUpdating = False ActiveSheet.Unprotect ("arnhem12") Set d = ActiveCell.CurrentRegion For Each c In d If c.Interior.ColorIndex = xlNone And _ c.Borders(xlEdgeLeft).Weight = xlThin And _ c.Borders(xlEdgeLeft).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeTop).LineStyle = xlContinuous And _ c.Borders(xlEdgeTop).Weight = xlThin And _ c.Borders(xlEdgeTop).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeBottom).LineStyle = xlContinuous And _ c.Borders(xlEdgeBottom).Weight = xlThin And _ c.Borders(xlEdgeBottom).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeRight).LineStyle = xlContinuous And _ c.Borders(xlEdgeRight).Weight = xlThin And _ c.Locked = False And _ c.Borders(xlEdgeRight).ColorIndex = xlAutomatic Then c.Value = 1 End If Next c ActiveSheet.Protect ("arnhem12") MsgBox ("Finished") Application.ScreenUpdating = True End Sub Greetings anita Roger Govier schreef: Sorry That "-" was meant to be an "=" set dd = activecell.currentregion -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Anita I think you need something like set dd - activecell.currentregion -- Regards Roger Govier "anita" wrote in message ps.com... Hi, I would like to have a macro which picks up my selected range in a workbook, similair to the activecell. So when I select a range in my workbook, it will be set as range in my macro. I've tried active.range but this doens't work (something like this: Set dd = Active.Range). I can't find it in the list properties/methods in vba. Could somebody help me? Thanks in advance. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Active range/selection?
Assuming all the line formatting is set as per the macro parameters,
then that suggests that the first cell is the only one which is unlocked. Check the others, (Format--Cells Protection Tab) and confirm that they are all unlocked and if so post back. Otherwise the macro would appear to be working correctly, i.e only setting a '1' for the only cell which is unlocked. HTH On 15 Sep 2006 05:37:46 -0700, "anita" wrote: Hi Roger and Richard, Thank you both for the quick respons. In my macro it doens't really work as I hoped it to be. When I run this macro he only activates the first cell and fills in a 1 if it has the beneath conditions. Maybe I'm doing something wrong. Below is my macro: Sub oke() Application.ScreenUpdating = False ActiveSheet.Unprotect ("arnhem12") Set d = ActiveCell.CurrentRegion For Each c In d If c.Interior.ColorIndex = xlNone And _ c.Borders(xlEdgeLeft).Weight = xlThin And _ c.Borders(xlEdgeLeft).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeTop).LineStyle = xlContinuous And _ c.Borders(xlEdgeTop).Weight = xlThin And _ c.Borders(xlEdgeTop).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeBottom).LineStyle = xlContinuous And _ c.Borders(xlEdgeBottom).Weight = xlThin And _ c.Borders(xlEdgeBottom).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeRight).LineStyle = xlContinuous And _ c.Borders(xlEdgeRight).Weight = xlThin And _ c.Locked = False And _ c.Borders(xlEdgeRight).ColorIndex = xlAutomatic Then c.Value = 1 End If Next c ActiveSheet.Protect ("arnhem12") MsgBox ("Finished") Application.ScreenUpdating = True End Sub Greetings anita Roger Govier schreef: Sorry That "-" was meant to be an "=" set dd = activecell.currentregion -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Anita I think you need something like set dd - activecell.currentregion -- Regards Roger Govier "anita" wrote in message ps.com... Hi, I would like to have a macro which picks up my selected range in a workbook, similair to the activecell. So when I select a range in my workbook, it will be set as range in my macro. I've tried active.range but this doens't work (something like this: Set dd = Active.Range). I can't find it in the list properties/methods in vba. Could somebody help me? Thanks in advance. __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Active range/selection?
Hi Richard,
Yes, that could be it. I the meanwhile I have found another solution, see below. I have use activewindow.rangeselection.address and it works. Thanks for your support and the time you've been putting in it. Sub oke() ' Keyboard Shortcut: Ctrl+e Application.ScreenUpdating = False ActiveSheet.Unprotect ("x") a = ActiveWindow.RangeSelection.Address Range("C13").Select Set d = Range(a) For Each c In d If c.Interior.ColorIndex = xlNone And _ c.Borders(xlEdgeLeft).Weight = xlThin And _ c.Borders(xlEdgeLeft).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeTop).LineStyle = xlContinuous And _ c.Borders(xlEdgeTop).Weight = xlThin And _ c.Borders(xlEdgeTop).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeBottom).LineStyle = xlContinuous And _ c.Borders(xlEdgeBottom).Weight = xlThin And _ c.Borders(xlEdgeBottom).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeRight).LineStyle = xlContinuous And _ c.Borders(xlEdgeRight).Weight = xlThin And _ c.Locked = False And _ c.Borders(xlEdgeRight).ColorIndex = xlAutomatic Then c.Value = 1 End If Next c ActiveSheet.Protect ("x") MsgBox ("Finished") Application.ScreenUpdating = True End Sub Greetings anita Richard Buttrey schreef: Assuming all the line formatting is set as per the macro parameters, then that suggests that the first cell is the only one which is unlocked. Check the others, (Format--Cells Protection Tab) and confirm that they are all unlocked and if so post back. Otherwise the macro would appear to be working correctly, i.e only setting a '1' for the only cell which is unlocked. HTH On 15 Sep 2006 05:37:46 -0700, "anita" wrote: Hi Roger and Richard, Thank you both for the quick respons. In my macro it doens't really work as I hoped it to be. When I run this macro he only activates the first cell and fills in a 1 if it has the beneath conditions. Maybe I'm doing something wrong. Below is my macro: Sub oke() Application.ScreenUpdating = False ActiveSheet.Unprotect ("arnhem12") Set d = ActiveCell.CurrentRegion For Each c In d If c.Interior.ColorIndex = xlNone And _ c.Borders(xlEdgeLeft).Weight = xlThin And _ c.Borders(xlEdgeLeft).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeTop).LineStyle = xlContinuous And _ c.Borders(xlEdgeTop).Weight = xlThin And _ c.Borders(xlEdgeTop).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeBottom).LineStyle = xlContinuous And _ c.Borders(xlEdgeBottom).Weight = xlThin And _ c.Borders(xlEdgeBottom).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeRight).LineStyle = xlContinuous And _ c.Borders(xlEdgeRight).Weight = xlThin And _ c.Locked = False And _ c.Borders(xlEdgeRight).ColorIndex = xlAutomatic Then c.Value = 1 End If Next c ActiveSheet.Protect ("arnhem12") MsgBox ("Finished") Application.ScreenUpdating = True End Sub Greetings anita Roger Govier schreef: Sorry That "-" was meant to be an "=" set dd = activecell.currentregion -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Anita I think you need something like set dd - activecell.currentregion -- Regards Roger Govier "anita" wrote in message ps.com... Hi, I would like to have a macro which picks up my selected range in a workbook, similair to the activecell. So when I select a range in my workbook, it will be set as range in my macro. I've tried active.range but this doens't work (something like this: Set dd = Active.Range). I can't find it in the list properties/methods in vba. Could somebody help me? Thanks in advance. __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Active range/selection?
Hi,
It looks like you could also simplify and replace all that borders stuff with If c.Borders.LineStyle = xlContinuous And c.Locked = False Then c.Value = 1 all on one code line Rgds On 15 Sep 2006 06:59:28 -0700, "anita" wrote: Hi Richard, Yes, that could be it. I the meanwhile I have found another solution, see below. I have use activewindow.rangeselection.address and it works. Thanks for your support and the time you've been putting in it. Sub oke() ' Keyboard Shortcut: Ctrl+e Application.ScreenUpdating = False ActiveSheet.Unprotect ("x") a = ActiveWindow.RangeSelection.Address Range("C13").Select Set d = Range(a) For Each c In d If c.Interior.ColorIndex = xlNone And _ c.Borders(xlEdgeLeft).Weight = xlThin And _ c.Borders(xlEdgeLeft).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeTop).LineStyle = xlContinuous And _ c.Borders(xlEdgeTop).Weight = xlThin And _ c.Borders(xlEdgeTop).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeBottom).LineStyle = xlContinuous And _ c.Borders(xlEdgeBottom).Weight = xlThin And _ c.Borders(xlEdgeBottom).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeRight).LineStyle = xlContinuous And _ c.Borders(xlEdgeRight).Weight = xlThin And _ c.Locked = False And _ c.Borders(xlEdgeRight).ColorIndex = xlAutomatic Then c.Value = 1 End If Next c ActiveSheet.Protect ("x") MsgBox ("Finished") Application.ScreenUpdating = True End Sub Greetings anita Richard Buttrey schreef: Assuming all the line formatting is set as per the macro parameters, then that suggests that the first cell is the only one which is unlocked. Check the others, (Format--Cells Protection Tab) and confirm that they are all unlocked and if so post back. Otherwise the macro would appear to be working correctly, i.e only setting a '1' for the only cell which is unlocked. HTH On 15 Sep 2006 05:37:46 -0700, "anita" wrote: Hi Roger and Richard, Thank you both for the quick respons. In my macro it doens't really work as I hoped it to be. When I run this macro he only activates the first cell and fills in a 1 if it has the beneath conditions. Maybe I'm doing something wrong. Below is my macro: Sub oke() Application.ScreenUpdating = False ActiveSheet.Unprotect ("arnhem12") Set d = ActiveCell.CurrentRegion For Each c In d If c.Interior.ColorIndex = xlNone And _ c.Borders(xlEdgeLeft).Weight = xlThin And _ c.Borders(xlEdgeLeft).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeTop).LineStyle = xlContinuous And _ c.Borders(xlEdgeTop).Weight = xlThin And _ c.Borders(xlEdgeTop).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeBottom).LineStyle = xlContinuous And _ c.Borders(xlEdgeBottom).Weight = xlThin And _ c.Borders(xlEdgeBottom).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeRight).LineStyle = xlContinuous And _ c.Borders(xlEdgeRight).Weight = xlThin And _ c.Locked = False And _ c.Borders(xlEdgeRight).ColorIndex = xlAutomatic Then c.Value = 1 End If Next c ActiveSheet.Protect ("arnhem12") MsgBox ("Finished") Application.ScreenUpdating = True End Sub Greetings anita Roger Govier schreef: Sorry That "-" was meant to be an "=" set dd = activecell.currentregion -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Anita I think you need something like set dd - activecell.currentregion -- Regards Roger Govier "anita" wrote in message ps.com... Hi, I would like to have a macro which picks up my selected range in a workbook, similair to the activecell. So when I select a range in my workbook, it will be set as range in my macro. I've tried active.range but this doens't work (something like this: Set dd = Active.Range). I can't find it in the list properties/methods in vba. Could somebody help me? Thanks in advance. __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Active range/selection?
Hi richard,
Yes, that's better and much shorter. Thanks again. Greetings anita Richard Buttrey schreef: Hi, It looks like you could also simplify and replace all that borders stuff with If c.Borders.LineStyle = xlContinuous And c.Locked = False Then c.Value = 1 all on one code line Rgds On 15 Sep 2006 06:59:28 -0700, "anita" wrote: Hi Richard, Yes, that could be it. I the meanwhile I have found another solution, see below. I have use activewindow.rangeselection.address and it works. Thanks for your support and the time you've been putting in it. Sub oke() ' Keyboard Shortcut: Ctrl+e Application.ScreenUpdating = False ActiveSheet.Unprotect ("x") a = ActiveWindow.RangeSelection.Address Range("C13").Select Set d = Range(a) For Each c In d If c.Interior.ColorIndex = xlNone And _ c.Borders(xlEdgeLeft).Weight = xlThin And _ c.Borders(xlEdgeLeft).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeTop).LineStyle = xlContinuous And _ c.Borders(xlEdgeTop).Weight = xlThin And _ c.Borders(xlEdgeTop).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeBottom).LineStyle = xlContinuous And _ c.Borders(xlEdgeBottom).Weight = xlThin And _ c.Borders(xlEdgeBottom).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeRight).LineStyle = xlContinuous And _ c.Borders(xlEdgeRight).Weight = xlThin And _ c.Locked = False And _ c.Borders(xlEdgeRight).ColorIndex = xlAutomatic Then c.Value = 1 End If Next c ActiveSheet.Protect ("x") MsgBox ("Finished") Application.ScreenUpdating = True End Sub Greetings anita Richard Buttrey schreef: Assuming all the line formatting is set as per the macro parameters, then that suggests that the first cell is the only one which is unlocked. Check the others, (Format--Cells Protection Tab) and confirm that they are all unlocked and if so post back. Otherwise the macro would appear to be working correctly, i.e only setting a '1' for the only cell which is unlocked. HTH On 15 Sep 2006 05:37:46 -0700, "anita" wrote: Hi Roger and Richard, Thank you both for the quick respons. In my macro it doens't really work as I hoped it to be. When I run this macro he only activates the first cell and fills in a 1 if it has the beneath conditions. Maybe I'm doing something wrong. Below is my macro: Sub oke() Application.ScreenUpdating = False ActiveSheet.Unprotect ("arnhem12") Set d = ActiveCell.CurrentRegion For Each c In d If c.Interior.ColorIndex = xlNone And _ c.Borders(xlEdgeLeft).Weight = xlThin And _ c.Borders(xlEdgeLeft).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeTop).LineStyle = xlContinuous And _ c.Borders(xlEdgeTop).Weight = xlThin And _ c.Borders(xlEdgeTop).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeBottom).LineStyle = xlContinuous And _ c.Borders(xlEdgeBottom).Weight = xlThin And _ c.Borders(xlEdgeBottom).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeRight).LineStyle = xlContinuous And _ c.Borders(xlEdgeRight).Weight = xlThin And _ c.Locked = False And _ c.Borders(xlEdgeRight).ColorIndex = xlAutomatic Then c.Value = 1 End If Next c ActiveSheet.Protect ("arnhem12") MsgBox ("Finished") Application.ScreenUpdating = True End Sub Greetings anita Roger Govier schreef: Sorry That "-" was meant to be an "=" set dd = activecell.currentregion -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Anita I think you need something like set dd - activecell.currentregion -- Regards Roger Govier "anita" wrote in message ps.com... Hi, I would like to have a macro which picks up my selected range in a workbook, similair to the activecell. So when I select a range in my workbook, it will be set as range in my macro. I've tried active.range but this doens't work (something like this: Set dd = Active.Range). I can't find it in the list properties/methods in vba. Could somebody help me? Thanks in advance. __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| jump to active cell in other sheet . | Excel Discussion (Misc queries) | |||
| HOW TO COLOR ACTIVE LINE CURSOR IS ON WHEN MOVING | Excel Discussion (Misc queries) | |||
| How do I have an active cell highlight automatically | Excel Discussion (Misc queries) | |||
| HOW TO COPY 480 ACTIVE E-MAIL ADDRESSES CLM "G" ON AN ACTIVE EXCE. | Excel Discussion (Misc queries) | |||
| Unable to set the active cell from VBA | New Users to Excel | |||
Linear Mode
