Don't run if entire column is selected

[Elaine]

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.

[Dick Kusleika]

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.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-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.

[Elaine]

Thank you very much ****. This worked!

"**** 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.
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-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.
>
>
>

[Jim Thomlinson]

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.

[Elaine]

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.