Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 106
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 595
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 106
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 983
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 106
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA how to check if entire row is selected? Jack Sons Excel Discussion (Misc queries) 6 April 23rd 23 09:00 PM
how to colour the entire row with the selected word murtuza kahn Excel Discussion (Misc queries) 1 March 15th 06 10:37 PM
Macro to print a selected range, not entire worksheet James C Excel Discussion (Misc queries) 3 October 19th 05 10:12 PM
I want to sort selected cells in Excel, not entire rows. Aeryn635 Excel Discussion (Misc queries) 1 June 1st 05 07:58 PM
Highlight the entire row of a selected cell Kerrick Sawyers Excel Programming 1 February 25th 04 07:49 PM


All times are GMT +1. The time now is 05:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"