Convert numbers to text
 

Hello all,

I am trying to program a checkbox such that when its value is True, a range
of numbers is brought in from another sheet. There are a bunch of
checkboxes, and the total of the data pulled in is summed using the =SUM
function.

When the checkbox value is false, I want the numbers to stay there, but to
be converted to text so that they are not added up in the =SUM function. I
will also want them to be grayed out, but that I can do easily.

I'm having trouble with converting the numbers to text, because I can't get
them to format the exact same way. I want parentheses for negative numbers,
a dollar sign, and no decimals. When I use quotation marks inside the =TEXT
function, I get a syntax error. What am I doing wrong?

My code is:

If CheckBox1.Value = True Then
Sheets("Sheet1").Range("E7:L7").Value =
Sheets("Sheet2").Range("F16:M16").Value
Else
Sheets("Sheet1").Range("E7").Formula = _
"=Text(Sheet2!F16," _($* #,##0_);_($* (#,##0)")"
Sheets("Sheet1").Range("F7").Formula = _
"=Text(Sheet2!G16, " _($* #,##0_);_($* (#,##0)")"
Sheets("Sheet1").Range("G7").Formula = _
"=Text(Sheet2!H16, " _($* #,##0_);_($* (#,##0)")"
Sheets("Sheet1").Range("H7").Formula = _
"=Text(Sheet2!I16, " _($* #,##0_);_($* (#,##0)")"
Sheets("Sheet1").Range("I7").Formula = _
"=Text(Sheet2!J16, " _($* #,##0_);_($* (#,##0)")"
Sheets("Sheet1").Range("K7").Formula = _
"=Text(Sheet2!L16, " _($* #,##0_);_($* (#,##0)")"
Sheets("Sheet1").Range("L7").Formula = _
"=Text(Sheet2!M16, " _($* #,##0_);_($* (#,##0)")"
End If

Convert numbers to text
 

"=Text(Sheet2!F16,"" _($* #,##0_);_($* (#,##0)"")"

double double quotes inside a string

--
Regards,
Tom Ogilvy



"MatthewB" wrote:

Hello all,

I am trying to program a checkbox such that when its value is True, a range
of numbers is brought in from another sheet. There are a bunch of
checkboxes, and the total of the data pulled in is summed using the =SUM
function.

When the checkbox value is false, I want the numbers to stay there, but to
be converted to text so that they are not added up in the =SUM function. I
will also want them to be grayed out, but that I can do easily.

I'm having trouble with converting the numbers to text, because I can't get
them to format the exact same way. I want parentheses for negative numbers,
a dollar sign, and no decimals. When I use quotation marks inside the =TEXT
function, I get a syntax error. What am I doing wrong?

My code is:

If CheckBox1.Value = True Then
Sheets("Sheet1").Range("E7:L7").Value =
Sheets("Sheet2").Range("F16:M16").Value
Else
Sheets("Sheet1").Range("E7").Formula = _
"=Text(Sheet2!F16," _($* #,##0_);_($* (#,##0)")"
Sheets("Sheet1").Range("F7").Formula = _
"=Text(Sheet2!G16, " _($* #,##0_);_($* (#,##0)")"
Sheets("Sheet1").Range("G7").Formula = _
"=Text(Sheet2!H16, " _($* #,##0_);_($* (#,##0)")"
Sheets("Sheet1").Range("H7").Formula = _
"=Text(Sheet2!I16, " _($* #,##0_);_($* (#,##0)")"
Sheets("Sheet1").Range("I7").Formula = _
"=Text(Sheet2!J16, " _($* #,##0_);_($* (#,##0)")"
Sheets("Sheet1").Range("K7").Formula = _
"=Text(Sheet2!L16, " _($* #,##0_);_($* (#,##0)")"
Sheets("Sheet1").Range("L7").Formula = _
"=Text(Sheet2!M16, " _($* #,##0_);_($* (#,##0)")"
End If