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
Chris
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
Chris
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:
http://www.appspro.com/utilities/Labeler.asp
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..
HTH
Chris
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
'**************************************************************
'* CODE TO HIGHLIGHT AND SHOW THE SELECTED RECORD *
'**************************************************************
With cboSerNo 'Clear the ComboBox
.RowSource = [serial_no].Address(external:=True)
.Value = ""
.SetFocus
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!
Chris
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!
Chris
Beat me to it Will! :wink:
I was looking for one of the MANY threads on this very subject, and found this one:
http://www.ozgrid.com/forum/viewthread.php?tid=6047
There are loads more if you search for SUMPRODUCT.
HTH
Chris
Sure you can, Ziz! Try the attached.
If you click anywhere in Sheet1 the MsgBox directs you to Sheet2 then activates it. Click Sheet1's tab and A1 contains a random number between 1 and 100, but you still can't select a cell.
Hope that's close to what you want! :wink1:
Yeah, just Unprotect it before you do stuff to it, then Protect it again when you hand control back to the Workbook. Something like this:
Private Sub Do_Stuff_On_The_Sheet()
ActiveSheet.Unprotect
.....Do Stuff To It
ActiveSheet.Protect
End Sub
How's that?
Chris
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")
Sheets("Sheet2").Activate
End Sub
See if that suits.
Chris
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:
Chris
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'
Crash
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?
Chris
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:
Cheers,
Chris
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:
=INDIRECT("OutputSheet!"&$F$9)
Any Good?
I'm as curious about this as you, now!
Chris
Hmmm...
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!
Chris
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
Chris
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!
Cheers,
Chris.
Good job he didn't do it 'round here - the sheep would've stolen his Beemer!
