selected range has different cell formatting

[Guest]

Hi all,
I wrote a macro that would change the colour and value of selected cell(s).
I want users to be able to use this macro only if the selected cell(s) are
not locked. (locked cells in the spreadsheet are the ones containing
formulas, and i don't want the users to change that). Here is the code i
wrote:

Sub Approved()

' check that selection is not a protected cell
With Selection
If .Locked = True Then
MsgBox "Sorry! You selected a locked cell."
Exit Sub
End If
End With

ActiveSheet.Unprotect
Selection.Interior.ColorIndex = 35 'Light Green
Selection.value = "Approved"

End Sub

The above code works well when the selection consists of one cell only, or
when the selection consists of a range of cells that all have the same
formatting (either ALL locked, or ALL unlocked). However, when i tested the
code by selecting a range that includes both locked and unlocked cells, the
code didn't seem to know the difference and was run according to the
formatting of the FIRST cell in the selected range.

What do i need to change in order to detect if there is at least one locked
cell in the selected range?

i'm using Excel 2003.

Many thanks
Tendresse

 

[Jim Cone]

If IsNull(.Locked) Or .Locked = True Then
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Tendresse" <[email protected]>
wrote in message
Hi all,
I wrote a macro that would change the colour and value of selected cell(s).
I want users to be able to use this macro only if the selected cell(s) are
not locked. (locked cells in the spreadsheet are the ones containing
formulas, and i don't want the users to change that). Here is the code i
wrote:

Sub Approved()

' check that selection is not a protected cell
With Selection
If .Locked = True Then
MsgBox "Sorry! You selected a locked cell."
Exit Sub
End If
End With

ActiveSheet.Unprotect
Selection.Interior.ColorIndex = 35 'Light Green
Selection.value = "Approved"

End Sub

The above code works well when the selection consists of one cell only, or
when the selection consists of a range of cells that all have the same
formatting (either ALL locked, or ALL unlocked). However, when i tested the
code by selecting a range that includes both locked and unlocked cells, the
code didn't seem to know the difference and was run according to the
formatting of the FIRST cell in the selected range.

What do i need to change in order to detect if there is at least one locked
cell in the selected range?

i'm using Excel 2003.

Many thanks
Tendresse

 

[Guest]

Jim, you are Gem. Thank you very much. it works very well now

 

[NickHK]

Read the Help for the possible values of the Locked property.
Hint: There is more than just True or False.

NickHK

 

[Guest]

Hi Jim, i'm stuck again in something similar to my previous question, and
need some help, please. I'm still a newy in VBA and feeling my way in the
dark here.

OK, this time i wrote a code to allow the user to change the selected cells
fill colour. However, to do that the selected cells have to be located in Row
9 only AND has to have NumberFormat = "General". (cells's formatting in row 9
is either GENERAL of TEXT). The code is:

Sub test()
Dim RowsCount As Integer, rowNum As Integer
With Selection
RowsCount = .Rows.Count
rowNum = .Row

If .NumberFormat = "@" Then
MsgBox "The cell's number format <> general."
Exit Sub
End If
End With

If rowNum <> 9 Or RowsCount <> 1 Then
MsgBox "Please ensure that your selection is from Row 9 only."
Exit Sub
Else
MsgBox "all is good."

End If
End Sub

My code that i wrote seems to always check the FIRST cell in the selected
range only! How do i detect if at least one cell in the selected range (not
necessarily the first one) is not formatted as GENERAL?

Many thanks
Tendresse

 

[Jim Cone]

The situation is almost exactly the same as for the locked cell issue.
As NickHK pointed out, the help file is worth reading first.
And it does point out that "Null" is returned from mixed cell formats...
(note: use a Long data type for "row" variables)
'--
Sub test_R1()
Dim RowsCount As Long, rowNum As Long
With Selection
RowsCount = .Rows.Count
rowNum = .Row
If IsNull(.NumberFormat) Or .NumberFormat <> "General" Then
MsgBox "The number format for all cells in the selection must be General. "
Exit Sub
End If
End With
If rowNum <> 9 Or RowsCount <> 1 Then
MsgBox "Please ensure that your selection is from Row 9 only."
Exit Sub
Else
MsgBox "all is good."
End If
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Tendresse"
<[email protected]>
wrote in message
Hi Jim, i'm stuck again in something similar to my previous question, and
need some help, please. I'm still a newy in VBA and feeling my way in the
dark here.
OK, this time i wrote a code to allow the user to change the selected cells
fill colour. However, to do that the selected cells have to be located in Row
9 only AND has to have NumberFormat = "General". (cells's formatting in row 9
is either GENERAL of TEXT). The code is:

Sub test()
Dim RowsCount As Integer, rowNum As Integer
With Selection
RowsCount = .Rows.Count
rowNum = .Row
If .NumberFormat = "@" Then
MsgBox "The cell's number format <> general."
Exit Sub
End If
End With
If rowNum <> 9 Or RowsCount <> 1 Then
MsgBox "Please ensure that your selection is from Row 9 only."
Exit Sub
Else
MsgBox "all is good."
End If
End Sub

My code that i wrote seems to always check the FIRST cell in the selected
range only! How do i detect if at least one cell in the selected range (not
necessarily the first one) is not formatted as GENERAL?
Many thanks
Tendresse

 

[Guest]

Hi Jim, thank you very much for your reply. Much appreciated.
Well, about reading the Help file, the IT dept in our organisation haven't
installed the help files for VBA on our computers and have somehow locked
access to online help! Which is very frustrating. So until i get my Access
Request (that i put forward to them) processed, i'm depending on information
through the Discussion Group.

In relation to your comment about using a Long data variable for row numbers:
Doesn't Integer values cover numbers from -32,768 to 32,767 (please correct
me if i'm wrong)? Do I still need to using Long data variable even if i'm
pretty sure that the number of used rows in my spreadsheets will never reach
32,767?

I'm actually glad that you raised this point because i often wondered if i
understood it correctly.

Thanks again for your help
cheers

 

[Jim Cone]

All thought, I have 4 different XL versions installed, I rely on XL 97 version help.
There is no wait while it decides to appear, usually provides the information
asked for (not something else) and is far better organized.
The instructions work in all current versions, while some xl 2003 help,
does not apply to earlier versions.

From XL97 VBA Help...
'---
"Row Property
Returns the number of the first row of the first area in the range. Read-only Long."

"Integer variables
are stored as 16-bit (2-byte) numbers ranging in value from -32,768 to 32,767"

"Long (long integer) variables
are stored as signed 32-bit (4-byte) numbers ranging in value from
-2,147,483,648 to 2,147,483,647.
'---

One of the things in this world you can rely on is, that once you release a program
for general use, it will be used in ways you never anticipated. Also, using an Integer
data type for a row number, forces Excel to convert it to a Long before use.

I would not wait for the IT department - buy a used copy of any version of John
Walkenbach's Power Programming for Excel books - you will be glad you did.

Jim Cone
San Francisco, USA




"Tendresse"
<[email protected]>
wrote in message
Hi Jim, thank you very much for your reply. Much appreciated.
Well, about reading the Help file, the IT dept in our organisation haven't
installed the help files for VBA on our computers and have somehow locked
access to online help! Which is very frustrating. So until i get my Access
Request (that i put forward to them) processed, i'm depending on information
through the Discussion Group.

In relation to your comment about using a Long data variable for row numbers:
Doesn't Integer values cover numbers from -32,768 to 32,767 (please correct
me if i'm wrong)? Do I still need to using Long data variable even if i'm
pretty sure that the number of used rows in my spreadsheets will never reach
32,767?

I'm actually glad that you raised this point because i often wondered if i
understood it correctly.

Thanks again for your help
cheers

 

[Guest]

Jim, thanks a million for your valuable information. I learnt a few new
things for which i'm very grateful.
cheers
Tendresse