I feel a bit silly asking this in the presence of so many obvious excel gurus: but here it goes. I've created some user forms, activex controls, etc. that do a series of complex calculations, supported by VBA and it's at the point where I want to roll it out. How do I hide / get rid of the active cell marker on the sheets? I've looked in the "options - view" section and have lucked out.
View 2 RepliesI have a dynamic chart which I would like to show markers in a SeriesCollection anywhere the backcolor of any of the corresponding source cells is ColorIndex = 36; otherwise no marker is shown. Something along the lines of:
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred
when copy/pasting, but IS if the keyword uses "quotes".
With Sheets("Chart").ChartObjects(1).Chart.SeriesCollection(1)
For i = 1 To UBound(.Values)
If "something here to check if .Interior.ColorIndex = 36" Then
.Points(i).MarkerStyle = xlMarkerStyleCircle
Else
.Points(i).MarkerStyle = xlMarkerStyleNone
End If
Next i
End With
whenever the active cell is within a given range, highlight the cell on the same row in column S (by changing its interior colour). This should occur each time the active cell is changed, whether by cursor keys or mouse. The effect would be similar to the row and column highlights at left and top of the worksheet.
This action should be restricted to one sheet in the workbook.
It's for Excel 2003.
I have got stuck on one piece of my code and having trouble fixing... Overall I am trying to find variable station name in cell L2 of Sheet 2 in Sheet 1 and then select and copy the data from the data in "cell L2 of Sheet 2" to the last entry of that row. I have attached an example test spreadsheet of the data and a macro is within Sheet 1 called test1. Please note that cell L2 in Sheet 2 will always be different station name and the station list in Sheet 1 will change with differing station name.
The code I am using is:
[Code] .....
The code that is not working and bringing up an error is:
[Code] .....
Attached File : Copy of Testexample.xlsm
to create a chart which shows a current value in relation to it's annual high and annual low. I've created a bar chart and added a series with a value of 100. The next series I added is the value of the marker I would like to ride along the bar.
My question is how do I change the marker value series from a bar to just a marker? I would like the chart to look like this (except a bar, not a line):
__________
Is it possible to vary the size of data markers (eg spots) in a chart by their value?
View 2 Replies View RelatedI have a scatter plot in which there are, for example, twenty plotted points representing one of three major categories.
We're using a red square, green triangle and blue circle to represent our categories on the graph. Going in to every plot point and manually changing each point so that it has the proper color and style is very time consuming.
Is there a way to designate in the spreadsheet (in its own column or something) that I always want the plot point to be represented in a certain way (like a red square) when graphed?
I would like to insert images (like a questionmark symbol) into a spreadsheet I'm working on, and then when the user hovers the mouse over the questionmark a "help-text" pops up. I've noticed that it doesn't seem to be that simple making a tooltip, however, is it possible to replace the small red triangle that is inserted into the sheet to indicate a comment with an image?
View 14 Replies View RelatedI'm trying to use an image as a custom marker on an XY scatter chart. The jpg image is embedded on a separate worksheet and when I copy and paste it via code it seems to be arbitrarily resized. I've also tried to resize the embedded image and set the marker size but it always appears about 3 times the size of the original image. See CASE -0.5 below.
Set seriespoint = chtBaseArea.SeriesCollection(" Events").Points
For i = 1 To rngCombPointsY.Rows.Count
With seriespoint(i)
.HasDataLabel = True
.ApplyDataLabels Type:=xlDataLabelsShowLabel
.DataLabel.Text = rngCombPoints. Cells(i, 1).Value
.DataLabel.Position = xlLabelPositionAbove
.DataLabel.Font.Bold = True
.DataLabel.Font.Size = 8
Select Case rngVehicleY.Cells(i, 1).Value
Case -0.5
Set shpTemp = wrkshtGraphics.Shapes("Soyuz_Small_Icon")
shpTemp.Copy
.Paste
.MarkerSize = 10
I have a simple Excel 2007 column chart which has names listed along the y axis for each column. Associated with each name in a small table is a colour reference. Is it possible to add a small marker (say a triangle or something) that aligns with each column?
Or possibly (or in addition) colour each column differently and in accordance with the assigned colours? I'm doing this with some vba code. but if someone can point out the general principle I should be able to work that in.
I have a chart with 5 different series. Instead of editing each one, say the marker size, is there anyway I can change the marker size for all of the different series at the same time?
View 1 Replies View RelatedI'm recording a macro, so a line in my graph is a specific green. The marker fill won't retain the green, it always ends up blue. the lines and marker line is green. no matter what color I try the marker fill will always be blue. How to correct this?
View 6 Replies View RelatedI'd like to create a simple graph exactly like the one on the picture, but for the life of mehow to do this on Excel 2010.This is the data I have:
Seperation First target Second target
0 59.00% 0.00%
2 64.28% 40.62%
4 62.68% 48.99%
6 60.81% 65.83%
8 61.82% 70.21%
I have a macro that changes the marker style for all series in a scatter chart, chosen through an input box.
[Code].....
It works nicely, but I want it to exclude any series that do not have markers (i.e. lines used to indicate a reference value). I've tried an if-then statement after the With statement, but it will add markers to any line series:
[Code] ......
Basically, if the series has markers or line+markers, I want it to change the marker style, otherwise do nothing with the series.
a bit of code so that when I open a sheet (which takes data from another sheet) it hides rows which contain a zero in a certain cell?
View 10 Replies View RelatedIs there a way to hide a hyperlink in a cell that it blank, at the moment I have multiple cells like below that shows the hyperlinks
ResourceSummary!B12
ResourceSummary!B13
ResourceSummary!B14
i just need to hide a cell which contains a value in excel sheet.
View 14 Replies View RelatedI have 3 columns on my spreadsheet with 100 rows:
- each cell in the 3rd column has an equation in it as such:
=h4+f5-g5 which means that the current cell in column 3 is adding the value of the cell just above it + and - the values of the cells on the left to it. This works fine, the only problem is that even though nothing is entered in row 40 for example, there still is a value based on the last calculation in row 5 for example - this value shows all the way down to row 100. Anyways,
How can I hide those values in column 3 all the way down to row 100 if nothing is entered in the other 2 rows?
I need help on hiding a row if a cell value is 0 in a column
View 5 Replies View Relatedhow to hide formulas from cells without having to protect the sheet. When I tried to hide some formulas the end-user was unable to input numbers into the model because the whole sheet was protectedl.
View 3 Replies View RelatedI want to be able to hide a selection of cells, including the border formatting, when another cell has a particular value (in this case I want the cells visible only if the value in the cell is above 0). I've looked around and can't seem to find anything relevant.
View 2 Replies View RelatedI am trying to search the cells in Column A around rows 54 to 77. And if the cell says "Yes" it should hide the row. I think I'm on the right track, but can't seem to get it work.
[Code] .....
I am trying to hide columns where the Value in the cells on row 9 is "", that cell being populated with a formula where the result is "". However I am getting the Compile Error Message 'Next without For'. Any clues?
Sub GraphC()
Dim a As Integer
Dim ColumnVar As Variant
ColumnVar = Array("B", "C", "D", "E", "F", "G", "H", "I", "J", "K")
[Code] .........
Trying to hide columns that have a date in any cell: cell < TODAY() Or cell - 30 < TODAY()
Code:
Dim row As Range
Dim cell As Range
Dim col As Range
Dim lRw As Long
[Code] .......
Here is what I have so far (debugger highlights my CheckBoxLD.Visable arguments)
Yes, this is an activeX checkbox
Code:
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Worksheets("Dosing").Range("BM21").Value = 0 Then
CheckBoxLD.visable = False
Else: CheckBoxLD.visable = True
End If
End Sub
I have numerous tabs in this file, but all of the tabs all summarize to the first tab called "Annual Record".
So after "Annual Record", tabs follow as "WO1", "WO2", etc...
On the tab called "WO1", I have cell AJ5, which can have 3 status selected (Data Validation List), which is either Inactive, Open, or Completed. If cell AJ5 on "WO1" says "Inactive", then I want a named range on "Annual Record" to be hidden. Right now I have the range named "WorkOrder1". This named range is essentially rows 4-7. So if it's easier to hide rows vs. a named range, then so be it. I also would like it to happen automatically (perhaps what you would refer to as a change event). When cell AJ5 is change to either "Open" or "Completed", then the rows would unhide. I'm thinking that this would be repeated for tab "WO2". If cell AJ5 says "Inactive", then rows 8-11 on the tab "Annual Record" or the named range "WorkOrder2" would be hidden. And again, if the cell AJ5 is change to anything other than "Inactive", then the rows or range would unhide.
I need to hide 8 rows (30-37) based on a value in cell B28.
If the value is 2 then only show rows 30 and 31
If the value is 3 then only show rows 30, 31 and 32
Then continue this up to the user puts in the value of 8 and no rows are hidden.
I'm trying to come up with some code that will hide a number of rows within the range row 7 to row 15 based upon the value of cell G1.
e.g if G1 is 1 rows 8-15 are hidden, if it is 2, rows 9-15 are hidden i.e the number of rows visible within that range equals the value of G1.
I am making a accounts spreadsheet for an association. The account has an income and outcome column - and there is a balance column to calculate the overall funds.
My problem is when I type the formula for working out the balance - the formula is fine, its what happens after I drag the formula down the page
The code works fine and it displays the overall balance - but when I drag this formula down th page it displays the same balance for as far as I drag the formula down. I wish to only display the balance up until the last entry e.g...........................
I have a sheet that pulls data from an external source. I want it so that when a person clicks on a cell or a range of cells, it automatically locks and hides the formula. I don't want to lock it by using the conventional method because protecting it will cause the external data not to be able to populate the cells. So my thinking is that when the data is imported, and the user clicks on a cell, it will automatically lock and hide.
I need columns
A C E:BB to be locked and hidden as soon as someone clicks it. Is this possible?
I read somewhere that I may be able to do some type of "Private Sub Workbook_SheetSelectionChange" to achieve this.