I knew there had to be a function like that, but couldn't for the life of me find it. Have been on Excel all day today and numbers are starting to float off the screen, so methinks it's time to go home! (That's my excuse.)
It may not be the easiest way, but I've a non-VBA method. It will involve you adding an extra column for each column you wish to rank which contains the numbers in order using the SMALL() function. (Substitute for LARGE() if you want to rank in descending order)
In the rank column you then use MATCH() to find the position of the number you are looking for within the ordered list. I have done this for the first column in your spreadsheet. Obviously you should hide the extra columns, (column E in the attached).
The attached file collects some of the other useful information you can get in this way. Complete reference of all the WMI classes is available on the MSDN site at http://msdn.microsoft.com/libr…sdk/wmi/wmi_reference.asp
ah, so easy .. there's me trying to crack a sledgehammer with a nut.
Thanks a lot, Dzinja.
In the VB Editor, select the option button [do you mean checkbox?] and in the properties window, (Press F4 if not on screen), enter the cell address in the "ControlSource" property, (include the sheet name to be on the safe side e.g. "Sheet1!A1" - without the quotation marks!)
Then when either the cell or the option button is changed the other will update automatically.
I have written a macro (below) to automatically copy a worksheet into a new book. I want it to copy the worksheet, but replace any references to other sheets by the values, but leaves formulas relating only to cells on the same sheet intact. I have almost done this but have come across two problems.
1) When a cell contains more than 255 characters, the activesheet.copy method truncates the contents of that cell. How can I find out programmatically if this has happened and copy over all the correct text when so.
2) I search for external references with .Find/.Findnext method searching for "!". This enables me to kill two birds with one stone by also removing any errors (such as #VALUE!) on the destination sheet and replacing with zero, whilst legitimate formulae are just replaced with their values.
However, some of the cells refer to named ranges in the original workbook, and I don't know how to identify these and therefore also replace these formulae with the values. Any ideas?Code
Sub exportPage() ActiveWorkbook.ActiveSheet.Copy 'NB: Need to rectify problem where cells have more than 255 characters ActiveWorkbook.ActiveSheet.Unprotect Set replaceMe = ActiveWorkbook.ActiveSheet.Cells.Find("!", , , xlPart) 'Following loop replaces all external references with their actual values. 'NB: Doesn't pick up named references On Error GoTo exitLoop Do replaceMe.Activate If Not IsError(replaceMe.value) Then replaceMe.Copy replaceMe.PasteSpecial Paste:=xlPasteValues Else replaceMe.value = 0 End If Set replaceMe = ActiveWorkbook.ActiveSheet.Cells.FindNext Loop exitLoop: Application.CutCopyMode = False ActiveWorkbook.ActiveSheet.Protect End Sub
Carrying on from what Richie said, I have a technique I use in files I distribute with macros.
I create a new sheet on which I put a cover page with instructions on how to change macro security level and a warning the workbook will not work without it.
In the Workbook_Open sub I put code to hide the cover sheet and turn on sheet tabs, etc.. I then turn off (via Tools>Options) sheet tabs, scroll bars etc.. and making sure the cover sheet is the active I save the file. This way if someone loads the file without macros enabled all they see is the cover sheet. Obviously its straightforward to get at the rest of the file if they have any Excel experience but for non-tecchys its a quick and easy solution.
NB: You can also do things such as hide the sheets [using xlVeryHidden] and protect the workbook and vb projects to make it harder for people to access the rest of the file, and unprotect/unhide the relevant stuff in the Workbook_Open sub.
Also advisable is to put code in the Workbook_BeforeSave event to re-hide/protect everything, and a button on the cover sheet like "I have macros turned on.." which calls the unhiding procedure. The BeforeSave event should also make the cover page the active page, so that whenever the user saves the file this protection is maintained. Like I say, there are ways around this, but I find it good enough for the users I deal with.
I've worked out how to do this myself now, so no worries everyone.
1) I can easily get the record number and then use the DoCmd.GoToRecord VBA function.
2) There is an option in the forms properties window for "Navigation Buttons" which I can easily turn off.
I think this should be easy, but being an Excel user I don't know how to do it.
I have a form in Access which displays a single record from a single table. There are 3 extra drop-down boxes which from the values selected in them I can uniquely identify a record in the data set, (from the 3 values selected I can match that to a value in a uniquely indexed field which is not the primary key).
Two things I want to do..
1) When the drop-down boxes are changed I want to change the record displayed. As I said I can work out the value of a uniquely indexed field, and from that the record number but the Form.CurrentRecord propery seems to be read only. There surely must be an easy way to do this...
2) Remove the record number thing in the bottom left corner. (Users should only navigate the record set using the three dropdown boxes I have mentioned.)
You need to select the "Move and Size with Cells" option in the Format Control>Properties menu.
Effectively, when Excel hides a column it sets its width to zero, so if the Checkbox has this option set, its size will also be zero - thus hidden. And it restores to proper size when column is unhidden. Note that the checkbox sizes will also change if the column width is changed.
Try this one:
There is a problem with this when there are more than 25 records, but less than 25 of them have values in the AX column. In this case it copies all the records over. I don't know if this is OK for your application but can't think off hand how to just copy 25 of them.
Apologies for the long post, but I hope you'll find it worthwhile...
I have designed a spreadsheet to easily allow you to control the Office Assistant from within Excel. I have used it to provide a complete online help system for an extremely large Excel project I did last year. :thumbcoo:
Anyway, to use it in your projects simply copy the helpData sheet and BalloonHelp module into your own spreadsheet, and you're pretty much ready to go.
You define your help balloons on the helpData Sheet - one per line starting in Row 2. (The macros currently use a lot of looping which I realise should be altered to use the Find function, but haven't got round to doing just yet!)
Column A is simply a balloon identifier.
Columns B thru' G control the balloon display and what buttons/icon are present.
The next 10 columns control the labels displayed in the balloon. If the balloon type is "0 - Buttons", these labels are clickable. The LabelN section is obviously the text you wish to appear. The ActionN column is what should happen when this button is clicked, (action ignored if the balloon type is Bullets or Numbers). This action can either take the form of the name of another balloon to display, or to run a macro enter Run:macroName in this cell. The macro you call should accept a single parameter - an array(1 to 5) of boolean.
The next 10 columns (R-Z) allow you to have up to 5 checkboxes on your balloon. The CheckBoxTextN is obviously the text next to the checkbox, and CheckedN is the default starting of the Checkbox (TRUE or FALSE). The array passed to macros talked about above is an array relating to the state of these 5 check boxes.
Under the buttons columns (F) you can choose what buttons you wish to have on the balloon. The next 15 columns, (AB - AP), control what happens if one of these buttons is selected. In the same way as the Label actions, these action should be either the name of the next balloon to show, or of the form Run:macroName. [If any action is left blank then the balloon closes and nothing else happens.]
Once you've set up your balloons to display them simply call the DisplayHelpBalloon procedure with the id of the first balloon to display (as text) as the argument.
I found it quite exciting and now use the Office Assistant for all sorts of thing - not just providing help but also as a different way of getting user interaction, and q&a processes for data entry. I hope you can understand all of this but feel free to email if you've any questions.
My first project where I used this consisted of a spreadsheet with around 15 output pages. I defined a complete online help system using this method, and had a toolbar button which basically ran a macro looking like this
This was an extremely easy way to write context sensitive help, and no HTMLHelp compiler in sight.
I would like to hear if any of you manage to put this, (or a derivative), to any interesting use.
Enjoy ... Dzinja.
You're trying to confuse me by changing your posts aren't you?!
Anyway, try this code..
Should do the job. I'm assuming there aren't any blanks in the list you want to select. If there are try the following
Hope this helps, Dzinja
In a blank cell type 0 [The number zero], then right click and select copy.
Select the cells with the numbers stored as text, (A2 thru' A5 in your example), then PasteSpecial selecting "Add" as your special option. This should convert your text to actual numbers.
Hope this helps, Dzinja
I came across a similar problem very recently. I have enclosed a file which shows how I got around it. I'm afraid I've not got time to explain it now, but do post (or email me) if necessary and I can get back to you tomorrow,
Assuming the no. of barrels is in cell A1 try this
NB: This does not work if cell A1 is blank as apparently 25,000 is less than a blank value. To trap this error you could wrap the whole thing in a
IF(ISBLANK(A1),0, ... ) statement obviously replacing the ... with what is above.
Hope this helps, Dzinja
I know you said this was closed, but you could quite simply do the same thing as Richie's code with
assuming A1 and B1 contain the text to be concat'd.
You can then autofit by double clicking the column width adjustment, or just make the column wider than any of the entries in either A1 or B1 are likely to be. (NB: You need to have "Wrap Text" selected in the Format Cells.. option)
Hope this helps, Dzinja
I have tried this, but it only works when I am trying to expand the range of a particular series. I want to automatically add a new series to a line graph when it is typed into the data sheet.
Taking the attached spreadsheet as an example, I want my chart to automatically add a 3rd series (line) to the graph when some data is entered in Row 4, which is currently blank.
I have tried defining a dynamic range to cover the whole data section, (see name "graphData" in file). I then put this as the Chart's "Source Data" but Excel automatically converts this back to a fixed range - Perhaps I am missing something?
Unfortunately, I also need to display the Legend, otherwise I could have just selected a much bigger area than required. Since the excess is blank it would not plot, but there would still be a corresponding blank legend entry.
Hope this now makes sense, Dzinja.
Unloading a form is very straightforward:
... even works if form is hidden after using UserForm1.Hide, and doesn't throw up nasty messages if the form isn't loaded. (At least it doesn't on Excel XP)