Toggle box and its use in a formula

[mjwillyone]

Our organization has employees who live and work overseas. When they
complete expense reports they often include purchases made using
foreign currency. The Excel spreadsheet form they use provides a place
where they enter the currency exchange rate. Some times this is a
whole number, sometimes it is a decimal number. It realy depends upon
where the live.

There currency determines whether the form needs to divide or multiply
by the exchange rate to determine its USD (US Dollar) equivalent.

Is there a way that I can create toggle boxes whereby the employee can
select whether the currency conversion formula multiplies or divides to
get the USD? Obviously, only one of the boxes should be able to be
checked and of course one must be checked at all times.

Also, once the box is set up, how do I create the formula? It should
basically do the following:

If the divide box is checked, divide the overseas currency by the
exchange rate. If the multiply box is checked, multuply the overseas
currency with the exchange rate.

Thank you so much for your help. It is most appreciated

Mike

 

[Earl Kiosterud]

Mike,

Build two Option Buttons using the Forms Toolbar. Right click each, Format,
and set its cell link to H1. Now you should get 1 or 2 depending on which
is selected.

Amount Rate USD
=IF(H1=1,A2/B2,A2*B2)

You can change the captions in the Option Buttons to "Divide" and
"Multiply."

This seems error-prone. Are you sure that all the folks understand what
divide and multiply mean regarding the exchange rate? It might be better to
have a cell where they put in the amount of foreign currency that's equal to
1 USD, and another where they put in the amount of USD that one of the
foreign currency is equal to. They'd use one or the other. Then we can
build a formula that figures out whether to divide or multiply. Less errors
is better than more errors! What think you?

Earl Kiosterud
mvpearl omitthisword at verizon period net

 

[mjwillyone]

Earl,

Your suggestion is great. Now, how do I set it up?? Employees decid
which way they view the exchange rate (i.e. .85 or 1.176). Your ide
would work wonderfully, provided that the spreadsheet will only allo
one number at a time in either of the two boxes.

Please let me know how to set it up to work this way, rather than m
previous way.

Thanks so much,
Mik

 

[Earl Kiosterud]

Mike,

I'll put the $ to one foreign in H1, and the Foreign to one $ in H2. To
ensure that only one box is used, Select H1:H2, Data - Validation - Custom,
and enter either of these:

=NOT(AND($H$1<>"",$H$2<>""))
=NOT(AND($H$1:$H$2<>""))

FOr the conversion, where the foreign amount is in A2, dollars is given by:

=IF($H$2<>0,A2/$H$2,A2*$H$1)

 

[mjwillyone]

Earl,

You have been a great help. I have another question for you:

I am creating an Excel spreadsheet that will be distributed to othe
staff members. The spreadsheet is basically a multi-page (tab) monthl
expense report. While each tab represents a particular form (busines
expenses, mileage logs, etc), not all of them will be filled in by th
employee. When we receive the completed reports, they are converted t
Adobe Acrobat format and saved to our office LAN environment.

I would like to fiind a way to change the text color of each tab nam
based upon the presence of a value greater than zero on its page. Fo
instance, if the employee does not complete the mileage log found o
tab 2, the tab text "Mileage Log" would remain black. However, if th
employee does complete the log, then there would be a mileage total o
the sheet. Because the total is greater than zero, I would like the ta
text to be colored Red.

The secretary who converts the Excel pages to Adobe Acrobat woul
simply select only the red-colored tabs to convert.

Thanks,
Mik

 

[Dave Peterson]

You want the color or the worksheet tab to change based on a value in a cell of
the same worksheet?

If yes, then put this code under the ThisWorkbook module:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Select Case LCase(Sh.Name)
Case Is = "sheet1", "sheet2": Exit Sub
End Select

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Sh.Range("a1")) Is Nothing Then Exit Sub

If IsNumeric(Target.Value) _
And Target.Value > 0 Then
Sh.Tab.ColorIndex = 3
Else
Sh.Tab.ColorIndex = 56
End If

End Sub

The "Select case" portion stops sheet1 and sheet2 from being processed. (You
may have some sheets that should not have the color changed.)

 

[mjwillyone]

Dave,

I am not certain what you mean by butting this code under th
ThisWorkbook module. I attempted to add the text you gave under th
View Code option when I right-clicked over the first tab in m
spreadsheet but then I got lost.

Help!

Thanks,
Mik

 

[Dave Peterson]

Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
Click on that to select it.

Now hit the asterisk (*) key on the numeric keypad.

Your project will expand to show all its components. One of those components
under "Microsoft Excel Objects" will be called ThisWorkbook.

Put the code in there.

But find where you put it before and delete it from there.

Debra Dalgleish has some pictures that may help you see what's going on. Her
website is actually for a different reason, but the pictures may be helpful.

http://www.contextures.com/xlfaqMac.html#NoMacros

 

[mjwillyone]

Dave,

What do I modify in the code so that when page1 (called expense report)
has a value greater then 0 in cell H49 the tab will turn red. Page 2
(called mileage log) would have cell H41 greater than 0.

Also, will the code automatically run or do I need to activate it (like
a macro).

I have taken the liberty of sending you the Excel file I am working on
so you can see where I have entered your code.

Thanks,
Mike


+----------------------------------------------------------------+
| Attachment filename: excel - expense report form - public.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=372395|
+----------------------------------------------------------------+

 

[Dave Peterson]

First, I don't open attachments.
Second, the attachment didn't make it to the newsgroups (thank goodness).
Third, these are event macros. They sit in the background just waiting for you
to do something so that they can go to work. So you don't have to run them with
Tools|macro|macros... stuff.

But you could use these two macros. Since the code is essentially the same for
each sheet--but the addresses are different, I think I'd give each cell the same
range name (but make each a worksheet level name so they really are different.

I chose "celltocheck" as the range name for each cell.

You could name this cell via the insert|name dialog or use this macro that
should be placed in a General module--not behind the worksheet--not behind
thisworkbook.


Option Explicit
Sub RunOnceAndNeverAgain()

With Worksheets("expense report")
.Range("H49").Name = "'" & .Name & "'!CellToCheck"
End With

With Worksheets("mileage log")
.Range("H41").Name = "'" & .Name & "'!CellToCheck"
End With

End Sub

You run this once and forget about it--well, until you redesign your workbook.

But here's the code (still goes behind the ThisWorkbook module):

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

Select Case LCase(Sh.Name)
Case Is = "expense report", "mileage log"
Case Else
Exit Sub
End Select

If Intersect(Target, Sh.Range("celltocheck")) Is Nothing Then
Exit Sub
End If

If IsNumeric(Target.Value) _
And Target.Value > 0 Then
Sh.Tab.ColorIndex = 3
Else
Sh.Tab.ColorIndex = 56
End If

End Sub

This is the code that's always waiting for you to make a change to the worksheet
(but only if you have macros enabled).