Drop Down Will Make a Window With Information Appear

[clayt101]

Hello all,
I am wondering if this is possible.
Here is what.

There will be a drop down menu with several codes.
When you choose one of the codes, a window will pop up letting you know how much money you have left to spend in that code.
The window could either be a box that forms automatically or maybe one of those yellow notes.
I would also need the information contained in the window to automatically update (as in each purchase subtracts from the total....I imagine that this would be done on a separate sheet, that would redirect to the informational window.
Thanks,
Clay

 

[My Aswer Is This]

You said:
"There will be a drop down menu with several codes.
When you choose one of the codes, a window will pop up letting you know how much money you have left to spend in that code."

Are you wanting to create a system where you enter how much you just spent and on what and then keep a running total of how much you have spent and on what, and then also show you current balance?

And you said:
choose one of the codes
I know of no way to choose codes. You can click on a button which has code in it that does what the code says.

 

[Eric W]

You could do something like this:

On some sheet, set up a list of codes and available amounts, like I did in G1:H6. Then somewhere else, probably a different sheet, go to the Developer tab, go to Insert, and under Form Controls, select Combo box. Use the mouse to "paint" the combo box where you want it, I have it span C3:D3 roughly. Then right click on it and select Format Control. Put G2:G6 for the input range, and select a cell for the code to go, I used E2. Then right click on the combo box again, select Assign Macro, and click New. It will open up the VBA editor to a new macro skeleton. Insert the following code:

Sub DropDown3_Change()
Dim code As Long, AmtAvail As Double, AmtToSpend As Variant
Dim sht As Worksheet

    Set sht = Sheets("Sheet9")
    code = sht.Range("E2")
    sht.Range("E2").ClearContents
    AmtAvail = sht.Range("H1").Offset(code)
    
    AmtToSpend = InputBox("You have " & Format(AmtAvail, "0.00") & " available in " & _
                           sht.Range("G1").Offset(code).Value & _
                           ".  How much do you want to spend? ", "Budget", 0)

    Select Case AmtToSpend
        Case "", 0
            Exit Sub
        Case Is > AmtAvail
            MsgBox ("Insufficent funds")
            Exit Sub
        Case Else
            sht.Range("H1").Offset(code) = sht.Range("H1").Offset(code) - AmtToSpend
    End Select
                           
End Sub

Change the sheet and cell references to match your sheet.

This should now do what you asked. If you click the drop-down, it will give you a list of codes. If you select one, it will kick off the macro, which will check the amounts and see how much is available, and ask you how much to use. It will then decrement the amount. Depending on what you want, you can create a transaction register somewhere at the same time.

Anyway, Excel can do all kinds of things like this, it just takes a little experimentation.

 

[clayt101]

Thanks Eric.