Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't run if entire column is selected
I have a small macro which formats selected cells. How I can get an error
message if an entire column is selected? I don't want all 65000 odd cells in that column to be formatted. I see that Columns("E:E").Select selects all cells in column E but I don't know in advance which column will be selected as the format macro will work on any block of cells selected. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't run if entire column is selected
Elaine
You can test for the number of cells in the selection. If Selection.Cells.Count < 100 Then 'Do stuff End If That might be better than checking if the whole column is selected. But, to answer your question: If Selection.Address < Selection.Rows(1).EntireColumn.Address Then 'It's okay to do stuff End If In either of these cases, you may want to test Typename(Selection) = "Range" because if something other than a range is selected, you'll get errors. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Elaine wrote: I have a small macro which formats selected cells. How I can get an error message if an entire column is selected? I don't want all 65000 odd cells in that column to be formatted. I see that Columns("E:E").Select selects all cells in column E but I don't know in advance which column will be selected as the format macro will work on any block of cells selected. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't run if entire column is selected
Thank you very much Dick. This worked!
"Dick Kusleika" wrote: Elaine You can test for the number of cells in the selection. If Selection.Cells.Count < 100 Then 'Do stuff End If That might be better than checking if the whole column is selected. But, to answer your question: If Selection.Address < Selection.Rows(1).EntireColumn.Address Then 'It's okay to do stuff End If In either of these cases, you may want to test Typename(Selection) = "Range" because if something other than a range is selected, you'll get errors. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Elaine wrote: I have a small macro which formats selected cells. How I can get an error message if an entire column is selected? I don't want all 65000 odd cells in that column to be formatted. I see that Columns("E:E").Select selects all cells in column E but I don't know in advance which column will be selected as the format macro will work on any block of cells selected. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't run if entire column is selected
I have run into the same thing.... My solution is to select a sub range based
on the range they have selected and the used range of the sheet. Here is how I have set up to format all cells that intersect with the used range and the selected range... This function formats all cells at text Public Sub Convert() Dim rngCurrent As Range Dim rngToSearch As Range Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection) If Not rngToSearch Is Nothing Then 'rngtosearch.select 'Uncomment this line to see what is going to be converted Application.Calculation = xlCalculationManual For Each rngCurrent In rngToSearch If Left(rngCurrent.Value, 1) < "=" Then rngCurrent.NumberFormat = "@" rngCurrent.Value = Trim(CStr(rngCurrent.Value)) End If Next Application.Calculation = xlCalculationAutomatic End If End Sub Give this a try... It does not care if the user selects a single cell, a range of cells, a column, a row... HTH "Elaine" wrote: I have a small macro which formats selected cells. How I can get an error message if an entire column is selected? I don't want all 65000 odd cells in that column to be formatted. I see that Columns("E:E").Select selects all cells in column E but I don't know in advance which column will be selected as the format macro will work on any block of cells selected. Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't run if entire column is selected
Thanks, very much, Jim. This is really great. This particular solution is not
helpful for my current question. However, it is something that I have wondered -- how to format a non-contiguous entries in a column. With your code it only formats the used range of the column. Later if data is entered in the empty fields they are formatted. Thanks again! "Jim Thomlinson" wrote: I have run into the same thing.... My solution is to select a sub range based on the range they have selected and the used range of the sheet. Here is how I have set up to format all cells that intersect with the used range and the selected range... This function formats all cells at text Public Sub Convert() Dim rngCurrent As Range Dim rngToSearch As Range Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection) If Not rngToSearch Is Nothing Then 'rngtosearch.select 'Uncomment this line to see what is going to be converted Application.Calculation = xlCalculationManual For Each rngCurrent In rngToSearch If Left(rngCurrent.Value, 1) < "=" Then rngCurrent.NumberFormat = "@" rngCurrent.Value = Trim(CStr(rngCurrent.Value)) End If Next Application.Calculation = xlCalculationAutomatic End If End Sub Give this a try... It does not care if the user selects a single cell, a range of cells, a column, a row... HTH "Elaine" wrote: I have a small macro which formats selected cells. How I can get an error message if an entire column is selected? I don't want all 65000 odd cells in that column to be formatted. I see that Columns("E:E").Select selects all cells in column E but I don't know in advance which column will be selected as the format macro will work on any block of cells selected. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA how to check if entire row is selected? | Excel Discussion (Misc queries) | |||
how to colour the entire row with the selected word | Excel Discussion (Misc queries) | |||
Macro to print a selected range, not entire worksheet | Excel Discussion (Misc queries) | |||
I want to sort selected cells in Excel, not entire rows. | Excel Discussion (Misc queries) | |||
Highlight the entire row of a selected cell | Excel Programming |