Posts by Relman

    Hi Andy, thanks for the reply :)

    Yeah, that's about what I'd figured :( I'm still playing/fiddling/tweaking in an effort to make that A1:A10 range dynamic, just like a 'real' spreadsheet, but without success so far!

    BTW: Funny thing that you should be the one to reply, 'cos I bet you can guess where I'm going with this, and what my next post is likely to be :biggrin:;;)

    "If you want to confuse your audience, use statistics. If you REALLY want to baffle 'em, use charts"

    Cheers, Ace


    Hi All

    I'd like to have a table in a UserForm which the User can fill with values, which are then used in various calculations when a "Calculate" button is clicked.

    The finished form is going to be part of an Add-In, so I don't want to go the "Put data in a textbox, press OK, copy to a background sheet, clear textbox, repeat" route.

    The MSO Spreadsheet 9.0 object looks the biz, but I can't find out how to use it this way, only for putting spreadsheet data on a web page.
    I’ve put one on the UserForm, formatted just nicely, but I don’t seem to be able to return the values from the cells.

    I’ve got this code on the Button_Click() event:

    txtTotup.Value = WorksheetFunction.Sum(Spreadsheet1.Columns(1))

    If I’ve got 12, 12, 12 in cells A1 to A3, the textbox shows “12”, so I’m getting something at least!

    BTW the .Range Property doesn’t work, so

    txtTotup.Value = WorksheetFunction.Sum(Spreadsheet1.Range(“A1:A10”))

    gives an error.

    Any suggestions gratefully accepted


    Hi brivera, and Welcome to the board,

    You could rename the X-Axis labels using Rob Bovey's Most Excellent and Highly Desirable "X-Y Chart Labeler". Download it free from here:

    This will let you pick a data series and name the Axis points anything you like, from a list you supply. For example, you could have a chart with those dates on the X-Axis and a list of whose birthdays they are, and use the birthday list to show "Mom", "Dad", "Sis", "Auntie Bill", etc..



    If it helps, this is the code I've attached to the OK button:

    Private Sub btnOK_Click()
    Dim i As Integer, r As Long
    Dim ctrl As Control
    ' Look for Serial Number in "Lists" sheet
    If ([serial_no].Find(cboSerNo, , xlValues)) Is Nothing Then
    ' If no record with that number, create it
    With Sheets("Lists")
    [A65536].End(xlUp).Offset(1, 0).Select
    r = ActiveCell.Row
    For Each ctrl In Me.Controls
    If IsNumeric(ctrl.Tag) Then
    i = Val(ctrl.Tag)
    Cells(r, i) = ctrl.Value
    End If
    Next ctrl
    CopyRow 'Copy the entry just made to "DataTable" Sheet
    SortListSheet 'Sort "Lists" by Serial Number
    End With
    End If

    With cboSerNo 'Clear the ComboBox
    .RowSource = [serial_no].Address(external:=True)
    .Value = ""
    End With
    End Sub

    Hi All,

    I have a workbook with 2 sheets; “Lists” and “DataTable”, and a UserForm with several controls on it, one of which is a ComboBox “cboSerNo” populated with serial numbers from a dynamic range “Serial_No” based on column A of “Lists”.

    The User enters a value in cboSerNo or picks one from the drop-down and clicks the OK button. The code then checks to see if cboSerNo.Value exists in [Serial_No], and branches as follows:

    1. If it isn’t on “Lists”, the contents of the UserForm are appended to the bottom of “Lists”, the whole row is copied to “DataTable”, and “Lists” is sorted by Serial_No.

    2. If it’s already on “Lists”, cboSerNo is cleared and gets the Focus.

    What I’m trying to do is modify (2) so that;

    a. If cboSerNo.Value is in [Serial_No]
    b. Select the row where it appears
    c. Load the values from this ‘found’ row into the UserForm
    d. Unlock the appropriate controls for editing
    e. Make an “Edit” button visible and enabled.

    I’ve got no problem with (d) and (e), but steps (a) to (c) are completely beyond me!

    This must be bread-and-butter to you guys; any hints/tips/FAQ’s will be much appreciated!


    Hi ratpick,

    You're correct, LINEST is of the form y=ax+b.

    In order to use it for non-linear data, you need to use logs to linearise the equation.

    In the case of a Power (y=ax^b) trendline, do this:

    =LINEST(LN(y-values),LN(x-values),,TRUE) entered as a 2-cell array formula.
    The first cell returned is the slope (b) and can be used directly. The second value is actually LN(a), and to use it you need to transform it back by doinig EXP(a).

    I've just read that back, and it's a bit wooly! See the attached, which I hope will show it better! :tumble:

    Hope it helps!


    Just thought of something; make sure they go to Sheet 2 by doing this in the Selection_Change event:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox ("This Sheet is locked. Please make changes on Sheet 2")
    End Sub

    See if that suits.


    Hi zazie, and Welcome to the Forum.

    You can suppress the 'This sheet is locked..blah' message and replace it with your own Message Box like this:

    1. Unprotect the sheet.
    2. Right-click the sheet tab and select "View Code".
    3. Select Worksheet from the left-hand drop-down at the top of the code window - this will insert this procedure:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    End Sub

    4. Add this line in the gap:

    MsgBox ("This Sheet is locked. Please make changes on Sheet 2")

    or you can change Sheet 2 to whichever sheet the User is allowed to change.

    5. In the Right-hand drop-down at the top of the code window, Select "Activate" and amend the event procedure to read:

    Private Sub Worksheet_Activate()
    Application.DisplayAlerts = False
    End Sub

    6. Go back to your sensitive sheet and Protect it again.

    Now if you select any cell in the worksheet you'll get your custom message.

    Hope that helps! :wink2:


    Cheers Andy, that's fixed it!

    I modified it to Clng(cboSerialNo.Value), but left [Fix] with the square brackets ('cos that means it's a range) - and BINGO!

    Supplementary Question:

    I understand that Clng converts the variable to type Long, and that if I just have "=cboSerialNo.Value" then that should be text, but what if the data in Serial Number is a mixture of numbers and text, ie A101, 102, BX-103-Z, etc? Where (or how) is the best way to handle mixed data types?

    Edit: I've just change the formatting of the Serial_No column to Text and removed the "CLng" operator and it works fine for both data types! Woo-Hoo!

    Still Big Thankus Muchlius to Andy for pointing me in the right direction tho'


    Hi Folks,

    I know I'm thick, but I just can't get the attached to work.

    Its got a 2-column table, column A is named "Serial_No", and the whole table is named "Fix".

    Click the button and a UserForm pops up with a ComboBox linked to Serial_No, and a TextBox in which I want to display the Tail Number associated with the Serial number picked from the ComboBox - "Piece of cake!" you all shout. You just set the TextBox.Value in the ComboBox Change() event, like this:

    Private Sub cboSerialNo_Change()
    txtTailNo.Value = WorksheetFunction.VLookup(cboSerialNo.Value, [Fix], 2, 0)
    End Sub

    But that gives an error every time, either "unable to set VLookup property of WorksheetFunction class" if I specify the range name complete with sheet! name, or "Argument not optional" if I don't! :mad:

    How the Sam Hill can I fix this?


    Shame you couldn't be there; it was a fantastic show, and the weather was just perfect!

    I know Lincoln very well as it happens; I met my missus there in '73 and proposed to her over dinner at the White Hart - that's why we stayed there!

    We were married in '74 at All Saints' on Brant Rd, and her bro' lives just off Brant Road, so we visit quite often.

    Maybe next time we'll stay at his place and do the whole weekend!

    If you're ever in Ritz's, wear a big sandwich board saying "I'm RoyUK" and I'll buy you a pint :biggrin:



    I've got a feeling that it's because INDIRECT expects a string argument, so that:

    =INDIRECT(F9) works if F9 contains (say) $A$3, but fails if F9 contains ACT04.

    To make INDIRECT work, it needs to be something like:


    Any Good?

    I'm as curious about this as you, now!



    If the outputs spreadsheet isn't on "VisionACT", maybe you need to include the sheet reference in the range name, viz:

    Where F9 has the value VisionACT!ACT04
    Where F10 Has the value VisionACT!ACT04R
    Where F11 has the value VisionACT!ACT04C

    Failing that, it might have something to do with the Undo stack being cleared when you recalculate . . . Maybe "The Professor" can help?!

    I'm as stuck as you, now!


    Hi Will,

    I think your problem is caused by the INDIRECT and MATCH functions trying to look at a range which has zero length after you've Cleared Contents in ACT04.

    Perhaps you need to initialise ACT04 as a 1-cell range after you've Cleared it, maybe by making F9 = "VisionACT!$A$3".

    I've got a similar problem, charting dynamic ranges which get cleared occasionally. I had to include a sheet-specific reference in the cell looked at by the INDIRECT function, which is then overwritten when you put some data in the named range.

    Hope that helps


    Hi Roy!

    Just noticed you're in Lincoln - did you go to the Rockin and A-Reelin concert at the Castle on Saturday night?

    Mrs Relman and I went, and stayed at the White Hart after; only 200 yds to stagger to bed!

    We wanted to stay to see Quo on Monday, but I could only afford one night at TWH!

    Marmalade Rock!!! :music:

    Hi Dave, Ritchie et al,

    I'm trying to reply to a Q from "flemmo" on Hey! That's Cool! entitled "Data Validation: Using hyperlinks to fill in cells", but I don't have permission to post on that forum! :(

    Here's my reply and attachment:

    Try the attached xls. If you click in cell B2 you'll see a Drop-Down list of Part Nos; click one of them and the associated details will be displayed in cells B4 and B5.

    The DropDown is a Data Validation by List based on the named range "Part_Nos" from cells H2:H4.

    The formulas in B4 and B5 use VLOOKUP to extract the other data from the named range "DataList" in H2:J4.

    Hope that's close to what you're trying to do!