YTLE#84: A Few Data Validation Tricks

Hiya,

Does anyone have any flashy hints & tips to share with the forum? Although these are by no means revolutionary, here's a few of mine to help add that extra level of 'tart' to your spreadsheets:

1) add a colour to your worksheet tabs. (right click - tab colour) although not widely used, this can add a nice touch.
2) part formatted text in cells. If you click on a cell with some text, the text obviously appears in the formula bar above. What some users dont realise is that you can then select a part of that text within the formula bar, and change the formatting (font size, bold etc)
3) If you can follow it ok, as it's tricky, add a nice rev counter to your arsenol of impressive looking analysis:
http://peltiertech.com/Excel/Charts/SpeedometerXP.html


Please can people add a few more neat tricks onto the list!

Hello,

I am a new to Data Processing, one of my colleague is playing with words by using some tricks. like, if he wants to find any numbers like:

TASK 88-99-333

So, these type of numbers are numerous in a file, so he wants to find those numbers, so ,use to find like with TASK ^#^#-^#^#-^#^#^# And in the same way for letters with "$" symbol. Is there any other tricks in word to reducing the work and save time?

Waiting for your reply,
Krish

I am using autofilter in my code for 3 input boxes however I am having some issues. When one of the data field input is entered as a fraction, the autofilter cannot find a match and return no data. Is there a way to make Autofilter recognize the data as a fraction When autofilter returns values it includes all the matches, even duplicates. I would like to eliminate duplicate and return a unique value list if possible Below is a sample of the code I am using
Thank you for your help

PHP Code:

Private Sub cbo_guntype_input_Change()
Dim cSize As Range
Dim ws As Worksheet
Set ws = Worksheets("PJAM-Charge Options")
    Me.cbo_gunsize_input.ListIndex = -1
    With ws
        .AutoFilterMode = False
        With .Range("D1:G1")
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:=Me.cbo_guntype_input.Value
        End With
        .Columns("T").ClearContents
        .Columns("E:E").SpecialCells(xlCellTypeVisible).Copy .Range("T1")
        Application.CutCopyMode = False
        .AutoFilterMode = False
        'Create a Named Range
        .Range("T1").CurrentRegion.Offset(1, 0).Name = "Gunsize"
        'Sort Named Range
        .Range("Gunsize").Sort Key1:=.Range("Gunsize").Cells(1, 1), Order1:=xlAscending, Header:=xlNo
    
    For Each cSize In .Range("Gunsize")
        With Me.cbo_gunsize_input
          .AddItem cSize.Value
        End With
    Next cSize
    
    Me.cbo_gunsize_input.SetFocus
End With
End Sub
Private Sub cbo_gunsize_input_Change()
    Dim cTemp As Range
    Dim ws As Worksheet
    Set ws = Worksheets("PJAM-Charge Options")
    Me.cbo_temprange_input.ListIndex = -1
    With ws
        .AutoFilterMode = False
        With .Range("D1:G1")
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:=Me.cbo_guntype_input.Value
            .AutoFilter Field:=2, Criteria1:=Me.cbo_gunsize_input.Value
        End With
        .Columns("V").ClearContents
        .Columns("F:F").SpecialCells(xlCellTypeVisible).Copy .Range("V1")
        Application.CutCopyMode = False
        .AutoFilterMode = False
        'Create a Named Range
        .Range("V1").CurrentRegion.Offset(1, 0).Name = "TempList"
        'Sort Named Range
        .Range("TempList").Sort Key1:=.Range("TempList").Cells(1, 1), Order1:=xlAscending, Header:=xlNo
        
    For Each cTemp In .Range("TempList")
        With Me.cbo_temprange_input
          .AddItem cTemp.Value
        End With
    Next cTemp
    Me.cbo_temprange_input.SetFocus
    End With
End Sub 

I thought maybe we could start a thread where everybody shares their favorite tips, tricks, and keyboard shortcuts for excel. My favorite is the F2 keyboard shortcut for bringing the cursor into a cell instead of clicking on it.
What is yours?

Or, at least i'd think of them as tricks, since it is not obvious to me!

o Is there any way to find the address of the cell that is currently calling the UDF? Say, you set a breakpoint, and want to know which cell it is now calculating when it hits the breakpoint.
o Is there a way to set a conditional breakpoint on a particular cell as calling the UDF? For instance, only break when this function is called from cell A5, etc? (In the VBA editor).
o Is there a way to get the text of the argument that is in the UDF call from the sheet? For instance, if my UDF is declared as Function MyFun(arg1). Say, on the sheet it is called with =MyFun(Max(A5:A10)). In the function execution code, i will get arg1 as the value of Max(A5:A10). Within UDF(VBA) code, can i get at the text for the argument, which in this example is "Max(a5:a10)"?

Thanks,
tom

I know how to modify the mouse on control panel so that I get a shockwave every time I hit ctrl but how do I get a constant colored bubble surrounding my mouse and a clicking sounds when the left click is pressed?

I need to find out how I can do a formula so that whenever a certain column F says "paid" it will delete that whole line of information. Is there a possible way to do this?? THere are other things that I need to know how to do in this macro or formula also, but this is the main thing I need to figure out. If you happen to know how to do this PLEASE REPLY ASAP!!!!

Here's I want to know if this is possible. I have a 2 sheet below.
First is the Sheet1 with already have table.


and I have the Sheet2 which is blank Sheet


Now, my question is, if its possible that every time I have changes in "Sheet1", Like I did in Sheet1 I create a table, then is it possible that sheet2 will have a table too same with sheet1 (all data entered will the same) without touching "sheet2"?.

Thanks for help.

Since I use a real computer and not a Mac <anyone taking the bait?>, I am quite fond of keyboard shortcuts.

You know, when you start typing a formula in 2007 and Excel then comes up with a list of suggestions to choose from? I've often wondered how to deal with this list other than removing my hand from the keyboard, finding the mouse somewhere behind the coffee cup and the sandwich wrapper, shaking out the crumbs and finally managing to click the right option.

The VBE it does a similar thing. BUT ... in the VBE you can arrow down the list and hit space to make the selected parameter appear in the code window. In a worksheet though, when entering a formula you can't. If you hit space, it'll insert a space.

Drives me bananas.

Just found out that when entering a formula in a worksheet, you can arrow down and then need to hit TAB to confirm the selection from the list.

duhh. howszat for consistency, Micro$oft?? Keeps us on our toes, right?

I don't want to start a "my favourite keyboard shortcut" regurgitation thread, but if you have any odd things that irk you, please put them here.

cheers

I would like to ear about any great trick you would be using in your spreadsheets.

I have no idea anymore about how to improve one of my excel applications, but I feel the need to refresh it. A bit like Microsoft is doing so often: little new functionality but making it appear as totally refreshed! In this way I could re-motivate the users to learn and to think a little bit more.

My application is a recipe optimization program that I have improved for at least 10 years.
Users can specifiy almost any wishes they may have, like chemical constraints on products, market constraints, technical constraints. All this can be expressed by using wildcards and algebraic expressions to define the constraints.
For simple applications (say 10 components) there is little to learn, while for more complicated applications (several plants and several products, logistics, hundreds of components) it is beneficial to learn a little bit about wildcards and how expressions are evaluated in this program.
But as often, many people want the benefit without the effort of learning a few things: simplicity becomes more important than functionality. Therefore, I need to "encourage" the users to learn and I would like to try to make this application a little bit more funny or user-friendly or creativity-boosting. (maybe I give some training session, but I don't like that very much)

This application contains three main sheets:
- one data sheet describing the components: costs, analysis of all kinds according to user's wishes, it also contains the solution and some sensibilities
- one constraint sheet describing all the constraints in a long list, totally customisable, contains diagnostics and other sensibilities
- one definition sheet defining the frequently-used algebraic expressions (business related)

The other sheets are reports: summing up components by different criteria, cost analysis, scenario comparisons, ... mainly user-defined sheets based on 1 UDF and/or pivot tables and some templates.

For some intermediate versions I included some gadgets: navigation buttons, color management buttons to ease beamer presentations, comment fields, use of custom properties, and the like. I removed all these gadgets since they were not used. I recently tested an "explain button" that would provide explanations for some results combining numbers and words and showing how the numbers can be explained: not an easy job but little prospect that they will make my users happier.