Reverse True/False with VBA
 

Just a quick question that I couldn't find an answer to after searching
because of how often the words "true" and "false" appear in different
threads.

How, with VBA, can I reverse a cells value from True to False and the
other way around?

The cell in question is A11, the value will always be either true or
false. The below didn't work for me.
cells(11,"A")= -(cells(11,"A"))

The reason I ask this is because I have similar check boxes on two
different sheets, and I need their values to match. I couldn't use the
linked cell option because it wouldn't let me link the cell to a cell in
another sheet. Which is why I am using VBA to change the other cell's
value so that the other sheet's check box changes as well. If theres
an easier way to do this, then I am all ears for suggestions :).

Thanks for your help.


--
ph8
------------------------------------------------------------------------
ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871
View this thread: http://www.excelforum.com/showthread...hreadid=515233

Reverse True/False with VBA
 

Cells(11, "A") = Not Cells(11, "A") will alternate between TRUE/FALSE

"ph8" wrote:


Just a quick question that I couldn't find an answer to after searching
because of how often the words "true" and "false" appear in different
threads.

How, with VBA, can I reverse a cells value from True to False and the
other way around?

The cell in question is A11, the value will always be either true or
false. The below didn't work for me.
cells(11,"A")= -(cells(11,"A"))

The reason I ask this is because I have similar check boxes on two
different sheets, and I need their values to match. I couldn't use the
linked cell option because it wouldn't let me link the cell to a cell in
another sheet. Which is why I am using VBA to change the other cell's
value so that the other sheet's check box changes as well. If theres
an easier way to do this, then I am all ears for suggestions :).

Thanks for your help.


--
ph8
------------------------------------------------------------------------
ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871
View this thread: http://www.excelforum.com/showthread...hreadid=515233

Reverse True/False with VBA
 

ph8,
If your using CheckBox's from the Control ToolBox you can try this
code.. The Code goes in the "CheckBox1_Change() event. The value of the
checkbox will be copied the sheet and cell location.

HTH, Rick


Option Explicit

Private Sub CheckBox1_Change()
dim ws1 as worksheets

set ws1 = worksheet("Sheet1") << -- Change Name of Sheet
ws.cells(11,"A").value = CheckBox1.value ''

End Sub




"ph8" wrote in message
...

Just a quick question that I couldn't find an answer to after searching
because of how often the words "true" and "false" appear in different
threads.

How, with VBA, can I reverse a cells value from True to False and the
other way around?

The cell in question is A11, the value will always be either true or
false. The below didn't work for me.
cells(11,"A")= -(cells(11,"A"))

The reason I ask this is because I have similar check boxes on two
different sheets, and I need their values to match. I couldn't use the
linked cell option because it wouldn't let me link the cell to a cell in
another sheet. Which is why I am using VBA to change the other cell's
value so that the other sheet's check box changes as well. If theres
an easier way to do this, then I am all ears for suggestions :).

Thanks for your help.


--
ph8
------------------------------------------------------------------------
ph8's Profile:
http://www.excelforum.com/member.php...o&userid=19871
View this thread: http://www.excelforum.com/showthread...hreadid=515233

Reverse True/False with VBA
 

Thanks for the quick replies!

Rick,

Code
-------------------
Private Sub ocb_Military_Change()

Dim ws1 As Worksheets

Set ws1 = Worksheets("Detailed View") '<-- Mismatch
ws1.Cells(11, "A").Value = ocb_Military.Value

End Su
-------------------

Resulted in a type Mismatch error at that line. I tried using these a
well:
set ws1 = Worksheet(sheet1) '<-- Sub/Function not defined
set ws1 = Worksheet("Detailed View") '<-- Sub/Function not defined
set ws1 = Worksheets(sheet1) '<-- Mismatch

Couldn't get it to work it seems, which is unfortunate, as I believ
your way of solving the issue was better then my method. So if you ca
get it to work or find another way to make it work, please do share :).

Also, out of curiosity, whats the different between declaring ws1 a
"Worksheet*-s-*" or "Worksheet" (note plural vs not plural)?

Toppers,
That turned my True or False into -1. Not exactly what I was hopin
for. I was trying to do this without using an if/else statement. An
other ideas?

Does % do remainder division? If so I could just do...

dim i as integer
i = cells(11,"A")
i=(i+3)%2
cells(11,"A")=i

That would work, wouldn't it? Or would it give me a mismatch whe
"true" was trying to be stored as an integer

--
ph
-----------------------------------------------------------------------
ph8's Profile: http://www.excelforum.com/member.php...fo&userid=1987
View this thread: http://www.excelforum.com/showthread.php?threadid=51523

Reverse True/False with VBA
 

Ph8, I'm sorry there is a type o in the Code I sent you. Change the
First line to read

Dim Ws1 as Worksheet not ( Dim ws1 as Worksheets)

now the code should read as follows:

Private Sub ocb_Military_Change()
Dim ws1 As Worksheet

Set ws1 = Worksheets("Detailed View")
ws1.cells(11,"A").value = ocb_Military.Value

End Sub



"ph8" wrote in message
...

Thanks for the quick replies!

Rick,

Code:
--------------------
Private Sub ocb_Military_Change()

Dim ws1 As Worksheets

Set ws1 = Worksheets("Detailed View") '<-- Mismatch
ws1.Cells(11, "A").Value = ocb_Military.Value

End Sub
--------------------

Resulted in a type Mismatch error at that line. I tried using these as
well:
set ws1 = Worksheet(sheet1) '<-- Sub/Function not defined
set ws1 = Worksheet("Detailed View") '<-- Sub/Function not defined
set ws1 = Worksheets(sheet1) '<-- Mismatch

Couldn't get it to work it seems, which is unfortunate, as I believe
your way of solving the issue was better then my method. So if you can
get it to work or find another way to make it work, please do share :).

Also, out of curiosity, whats the different between declaring ws1 as
"Worksheet*-s-*" or "Worksheet" (note plural vs not plural)?

Toppers,
That turned my True or False into -1. Not exactly what I was hoping
for. I was trying to do this without using an if/else statement. Any
other ideas?

Does % do remainder division? If so I could just do...

dim i as integer
i = cells(11,"A")
i=(i+3)%2
cells(11,"A")=i

That would work, wouldn't it? Or would it give me a mismatch when
"true" was trying to be stored as an integer?


--
ph8
------------------------------------------------------------------------
ph8's Profile:
http://www.excelforum.com/member.php...o&userid=19871
View this thread: http://www.excelforum.com/showthread...hreadid=515233

Reverse True/False with VBA
 

Perfect! Thanks for your help, Rick.


--
ph8
------------------------------------------------------------------------
ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871
View this thread: http://www.excelforum.com/showthread...hreadid=515233