Posts by Relman

  • You're my Besht Pals, you are! [hic!]

    In my office, I'm considered to be a bit of an Excel Wizard:


    "How the hell can I do that?" "Dunno, ask Chris, he's an Excelspert!"


    or


    "Have you seen that Weibull Plotting Tool Chris made? Absolute genius!"


    But what they don't know is that almost everything I know about Excel came from the TRUE geniuses: you guys at Ozgrid!


    Mere words of thanks can't begin to express the gratitude and admiration I have for your helpfulness and expertise.


    So if you're ever near Hunts, look me up, and I'll buy you a huge bucket of :drink:


    Muchos Kudos Muchachos!


    Chris

  • VBA: Range is copied every time I change ANYTHING!!

    Thanks Neale for another almost instant reply - what time is it in Oz?


    If I change Change to Calculate (leaving ByVal Target As Range) I get "Procedure declaration does not match description of event or procedure havinig the same name"


    As a temporary workaround I've stuck the code to an "Update" button until I've nailed the UserForm design.
    If I Change Change to Calculate, and delete the declarations from the brackets, I get "Object Required"


    So I renamed Target to ActiveCell and the darned thing ran in circles until I hit Escape!!! :biggrin:


    So maybe I'll have to use some kind of form, either User or Data to make the changes and hold off the Copy/Paste until all the changes are done.


    Whadda ya think?


    Chris

  • VBA: Range is copied every time I change ANYTHING!!

    Hi Neale and Ritchie!


    Having played with my worksheet a bit, it's become obvious to me that WorksheetChange is probably not the place to put the copying routine you worked out for me!


    It fires when I change any cell (of course), but if I change every cell in a record, I get 14 new records in the Full History sheet (natch). :redface:


    SelectionChange can't be the place either, for the same reason - Help? (in a small, pleading voice)


    Chris

  • [Solved] VBA: Range Copy works on a Button but not on Worksh

    I’m building a Workbook with 3 sheets in it: “Current”, “Lookup Lists” and “Full History”.
    “Current” has a list of Aircraft Numbers in A, Serial Number of fitted widget in B, and various other info in C to N.
    The lists of Tail Numbers and Serial Numbers come from “Lookup Lists”, and use Derk’s Most Excellent method for ensuring that once an aircraft is in the list, you can’t add it again. Likewise, once you fit a Widget to any aircraft, it comes off the list so you can’t fit it to another aircraft.


    What I want to do is update “Full History” any time a change is made to any of the cells in “Current”, ie if I change the Widget fitted to an aircraft, a new line is added to the list in “Full History”


    Pseudo code for the WorksheetChange() event in “Current”:


    Select cells A to N of the row that’s just been changed
    Copy the selection
    Select the next blank row in “Full History”
    Paste the values
    End Sub


    What I’ve got so far is this:


    Private Sub Worksheet_Change(ByVal Target As Range)
    Range("A4:N4").Select
    Selection.Copy
    Sheets("Full History").Select
    Range("A65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    End Sub


    If I run this from a button, it works just fine, but in the WorksheetChange() event I get a “Select method of Range class failed” error. What am I doing wrong?



    Thanks in advance,


    Chris

  • Data Validation: Control Date Input on worksheet

    I saw a thread in the last week or so which had Data Validation applied to a cell based on a list of dates created during WorkbookOpen().


    In other words, the programmer decides the start and end dates, a loop runs and populates the DV list with the formatted date values, and Will's yer Uncle!


    I've since lost the code that was supplied, the link and my mind!


    It might not even have been here that I saw it, in which case I'll go back to Google and try all combinations of Date,Validation,List, etc.


    Here's hoping!


    Chris

  • [Solved] Formulas: Waiting to be activated

    How can I have a formula sitting dormant until data is put into another cell?


    The attached shows what I mean, I hope.


    The formula in col B is


    =IF(ISBLANK($A2),"",$A2^2)


    and in col C it's


    =IF(ISBLANK($A2),"",($A2^2)/2)


    These formulas are copied down to Row 25. If you enter or paste numbers into A2 to A25, the chart updates automatically.


    But I need to allow for an unknown number of data points in column A; could be 10 or 10,000!


    I tried Ctrl-Enter to fill B and C, but the worksheet bloats to about 6 Meg and takes yonks to calculate!


    Any suggestions?

  • AutoFilter: Display More Items in Drop-Down

    I've got a spreadsheet with over 35,000 lines (records) in it (Yes, I know, I'd be better off using Access!).


    AutoFilter only shows the first 1000 individual items in the Drop-Down; is there any way to increase this limit?


    P.S. If you guys can't do it, then it can't be done!! :wink1:

  • [Solved] Data Validation: Prevent Dupes if previous record e

    That's just the job! Thanks Derk! :bouncy:


    I made a slight change to make EligibleSer dynamic instead of fixed at G2:G24, but otherwise I'll use your solution as is.


    Roy; I tried a few of those methods, but they don't work when the cells are changed as a result of a formula - any way round this? Thanks for the steer, tho' :wink2:


    Cheers Guys, You da Men!

  • [Solved] Data Validation: Prevent Dupes if previous record e

    How can I prevent the User raising a duplicate record if a previous record is still “Open”?


    I hope the attached xls will show what I mean.


    At present, the User picks a Serial Number from the Data Validation List in column A of “Records”, enters a start date in column B, and the rest all calculate automatically. This record is “Open”


    When you enter a stop date in column C, or put a value in column D, then that record is “Closed”.


    What I want to do is prevent the User being able to pick, say, 101 from the Drop-Down if there is already an instance of 101 in column A, with nothing in C or D.


    Can this be done?

  • Formulas: Scientific and greeksymbols

    I'm always having to use all kinds of symbols in my line of work, so I was overjoyed to see how to run the Character Map from a Toolbar button in this month's PCHome magazine ("Character Map On Tap", Issue 139, page 33).


    In case you don't have a copy, create this Macro:


    Sub Charmap()
    Dim Runit
    Runit = Shell("charmap.exe", 1)
    End Sub


    Customise the Toolbar, add a button and assign that to it.


    Regards,


    Chris.

  • Controls (Control Toolbox): RefEdit Control doesn&#

    I've put a Forms Button on the sheet and assigned this macro to it:

    Code
    Public Sub GetRange()
Range("AddressLine").Value = Selection.Address
Range("B7").Select
End Sub


    which works just fine! :bouncy:


    Now, if I only knew the ColorIndex values and what colours they turn out like, I could fill in the selected range like this:

    Code
    Public Sub GetRange()
Range("AddressLine").Value = Selection.Address
Range(Selection.Address).Interior.ColorIndex = 4 'Or the value for tan/Light Blue or whatever
Range("B7").Select
End Sub


    Any suggestions for a table of ColorIndex values?


    Once again, Thanks for helping!