Posts by kotm14

Re: Toggle Conditional Formatting On/Off

Thank you Dave, but I don't think I understand how/where to do what you suggest.

Because I'm using a top10 condition, I don't know a way to add to the condition. There is obviously no place through the interface and when I examine the coded format conditions, I see no place to add my own terms to the conditions - everything appears to be defined by setting object attributes to system constants.

Will your solution effectively eliminate the de-selected rows from the calculation? It's not just a matter of not formatting the deselected rows, I also can't have the values of the deselected rows affect formatting of the selected rows. (For example, if I'm highlighting the top 50% of values and have 20 rows selected, I want to highlight the top 10 rows. If I deselect those 10 rows, I want conditional formatting to highlight the next 5 values (top half of 10 selected rows)).

Hi, my spreadsheet uses XL2007's conditional formatting "top10" to highlight the top 25% of values.

But it also allows users to de-select specific rows to evaluate options; if a row is deselected, I want to to effectively vanish (i.e. not affect the conditional formatting at all, so overriding the formatting isn't sufficient).

Removing it is simple: I have a Worksheet_Change macro delete the FormatConditions object.

But I can't find a good way to add the formatting back if the user re-selects the row. Because the top10 formatting must evaluate each cell against the others in the range, I can't just add formatting to the cell - I have to add the re-selected cell back to the range.

I wish the FormatConditions object had an active/inactive flag, but it doesn't appear to.

Thanks.

PS. It's easy to do the formatting in code, but I wanted to try to use the built-in formatting

Re: Limiting Cell Colour "expansion" And Getting Column Letters

Yup - I actually had to test cells one by one because some rows have coloured bars that extend through the region, but it was a simple loop and test very similar to what you suggested.

Thanks again!

Re: Limiting Cell Colour "expansion" And Getting Column Letters

Thanks - I know I can reset the interior colour, but my problem is I will have many rows with coloured regions starting and ending in different places. So if the colour expands out the side when I insert a column, I'll have to go over all the rows and reset the colour of all the rows in which the colour expanded. It's not difficult to do, but I was hoping to avoid it by just not expanding the colour in the first place.

You're right on the column letter - I don't need it but had an idea that the letter would make easier.

Thanks!

Hi, two quick questions:

If I have coloured cells and I insert a column to the left of them (using VBA), the colour does not "expand" into the new column (i.e. the cells in the new column do not inherit the colour of the cells to their right). But if I insert a column to the left of coloured cells, the colour expands into the new column/cells.

Is there a property I can set to prevent the colour from growing when I insert new columns beside coloured columns? If I insert a column between two coloured cells, I want the colouring to fill the gap automatically but I don't want the colouring to expand BESIDE coloured cells.

Second, there must be an easy way to get column letters but I couldn't find it in a search. To get the address, I'm using:

cell_ref = ws.Cells(1, cnum).Address(False, False, xlA1)

That returns "B1" etc. I know I can isolate the column letter with string functions, but there must be a way to just get it directly.

Thanks

Re: Help: Too Many Rows/Columns Error

Thank you.

Turns out the problem went away on a different machine with a (slightly) newer version of Excel. Don't know what caused it.

Someone at work sent me a spreadsheet but every time I try to change something, filter or hide rows etc. I get a message stating too many rows or columns.

Any way to suppress the error or divide the data into multiple sheets?

Thanks

Re: Finding Coordinates of Chart Points

I thought about that (and will try it) but there will be many, very short trendlines and I have already calculate the slope etc. for them so I thought it would be easiest to just plot the lines directly.

I'm playing with stock analysis using one of the standard Excel stock charts (xlStockHLC) that plots activity as vertical candlesticks.

I want to plot my own trendlines over the candlesticks - how do I find out the coordinates of the candlesticks (top and bottom coordinates)? I already have the chart object and have colour-coded the candlesticks - the coordinates must be there somewhere but I can't find them. Once I have them I assume I'll use the standard drawing functions to plot lines.

Thanks

OK, I just added the menu creation to the Workbook_Open routine and added a Workbook_BeforeClose routine to remove it when the add-in is removed.

The activate/deactivate are overkill because once the add-in is added, the menu will be there whenever Excel is open anyhow (I think).

Thanks again.

Thanks for all your help. I have now figured out the problem - but not the solution.

Sorry but I was slightly wrong in my description. I have a workbook_open procedure but I actually create the menu in a workbook_activate procedure (I also remove my menu with a workbook_deactivate procedure when the workbook is deactivated).

When I add the code as an add-in, my workbook_open procedure runs when the workbook is opened but the activate procedure does not hence no menu (obviously when the code is in an add-in, the workbook with the code is not activated). So I guess I have to activate the workbook from within the open procedure (can I do that?).

I can move the menu creation into the workbook_open procedure but I like the menu add/remove function when the user switches to a different workbook. However, I'm no longer sure the activate/deactivate procedures will run when I want them to any more.

I tried again but I get the same behaviour - my code is there but my menu doesn't appear; it all works when I open the spreadsheet I developed it in.

I notice that when I open the workbook I developed it in, my code is in the current workbook whereas when I open Excel after adding my code as an add-in, the current project is labeled "VBAProject (Book 1) and my add-in appears as a different project - is that the problem (is it not activating my project workbook)?

Thanks

PS. When creating graphics on a worksheet, is there an easy way to remove the gridlines (or a non-gridline worksheet) as a clean backdrop? I created a white rectangle as a backdrop but that looks klugey to me.

Hi,

I have an Excel VBA application (collection of functions/procedures, forms and a menu). How do I package it as an add-in so others can use it?

I tried just saving the workbook as an add-in (.xla) and had a problem. I added the add-in to my Excel (using Tools - Add-Ins) and everything looked right, but when I re-opened Excel the code was there but the menu (which is created by a workbook-open procedure) wasn't there.

Thanks

First, thanks for all the help over the last few weeks - I now have a set of UDFs and routines to do the analysis I need.

My final question is about the menu. I created my menu by drag-and-drop in the Excel interface; if I package my routines as an add-in, is there any way to include this menu or do I have to write a workbook-open procedure to create the menu and wrap that procedure into the plug-in.

Thanks again.

Thanks,

Sorry, I should have been more specific - I meant add points manually in a VBA subroutine.

The data I want to graph is based on but not directly the data in my spreadsheet. My plan was to analyze the data, then create the array of points and the SeriesCollection in VBA. However, I can't figure out or find how to create the points or add them.

Thanks again.

How can one add data points manually to create a chart? I can add the sheet and create the chart but I can't figure out how to create the array of data points and get them plotted.

My data sets overlap so many points actually have multiple shapes and labels overlapping so many are illegible and difficult to work with. I'm considering just putting a single point wherever one should be and creating a single label listing all the data sets it applies to.

thanks

Thanks - I created a UDF to do this.

Thanks - I went ahead and created a subroutine invoked by a menu item with a form to specify the output range.

I don't have anything yet to populate the output cells with the results because everything I tried didn't work. I'm trying to do this without making the user select the output range (although I can require that if necessary). It looks something like this:

Assume there is an r-by-c matrix of values on the active sheet; the user goes to an empty cell below the this matrix and enters the formula "func(input_range, par1, par2)" in cell (x, y); input range is the r-by-c matrix.

The results will be an (r+1)-by-(c+1) matrix that begins in cell (x, y). I have written all the math that calculates the results for each value in the matrix but I don't know how to actually fill the cells with the values that I have calculated.

What object or collection gives me access to all the cells on the active sheet and what is the syntax to set their values - attempts like "set rng.cells(x, y).value = x" don't work.

Thanks again.

This should be easy but...

How can I set the value of cells in VBA? I've tried various combinations of Cells(a, b).value = ..., Set Cell(a, b) = ... and so forth but nothing works. I can't even set a single cell value successfully.

The complicating factor is that I don't have a user-defined range for the output. My function/sub takes a rectangular range of any size as input, then calculates a matrix of values (very much like Excel's Correlation Coefficient matrix) - it's tricky for the user to know what size to make the output range.

What I'm HOPING to do is have the user enter the input range and arguments as a FUNCTION in a single cell (in an empty area of a worksheet) and have VBA create the output matrix with the ActiveCell (where the function was entered) as the top left cell of the matrix.

I've tried using ActiveCell.Offset(a, b) = ..., Cells(a, b) = ..., etc. but nothing works (partially because I think Cells() has to be part of a range). Can I set the value of cells without a defined output range? If not, how can I create an output range? Do I have to create an array function?

Thank you.