Thanks
That's not what I ended up doing, but it is what gave me the idea for my solution. Thanks andy.
Thanks
That's not what I ended up doing, but it is what gave me the idea for my solution. Thanks andy.
clarification
Im sorry I wasn't clear enough.
What I want to try and figure out is how to remove cell references from a RANGE OBJECT.
By this I mean,
-I have a Range type object that holds for example ("A1:A10")
-I want to remove "A3" from that Range object, and not the spreadsheet
-This will result in a Range with two areas, ("A1:A2" and "A4:A10")
The spreadsheet is unafected, just the range object.
How would someone Programatically add/remove items from a range object?
Here
Sheets.Add.Name = "NAME OF SHEET"
Is there a way to check if a specific range is within another range?
IE> Range1 = A1:A10
Range2 = A5
-> Check to see if Range2 exists in Range1
I'm wondering if there is a function/method that exists in the Range class.
heh
Wise guy
neat code
I actually ended up writing something that is pretty neat. You can define any type of error message you want from this code when the user modifies any range you specify. This is put in the "worksheet" object that needs to be checked against illegal range editing.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim Rng As Range
Dim Flag As Boolean
Flag = False
For Each Rng In Target.Cells
For Each cell In Worksheets("DATA").Range("A1:AZ10")
If cell.Address = Rng.Address Then
MsgBox "You are not authorized to edit cells in this range."
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Flag = True
Exit For
End If
Next cell
If Flag = True Then
Exit For
End If
Next Rng
End Sub
Display More
What is the best way to specify a range of cells that cannot be modified by the user?
I have a worksheet with a specific range of cells that i want to remain read-only.
my method
This is how I do what you have asked in my current project:
Dim Flag as Boolean
Flag = false
for each ws in Worksheets
if ws.Name = "Worksheet I'm searching for" then
Flag = true
exit for
endif
next ws
if flag = true then
'if worksheet is found, display the button
Set NewRowButton = cb.Controls.Add(msoControlButton, , , , True)
With NewRowButton
.Caption = "Add a new row"
.OnAction = "'" & ThisWorkbook.Name & "'!Utils.InsertNewRow"
.TooltipText = "Adds a new row and generates a new UID for the row"
.Style = msoButtonIconAndCaption
.FaceId = 319
End With
End If
Display More
Yes
This is possible, yet it is not a simple task for a beginner.
HEre is the basis of how to do it:
ActiveChart.SeriesCollection(1).points(25).ApplyDataLabels AutoText:=True, _
LegendKey:=False, ShowSeriesName:=False, ShowCategoryName:=False, _
ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False
The above code will format the First series, Point number 25 to show data labels. Now, lets say you wanted to do a check for each point, and set the label. Here is a better approach:
Dim nPts As Integer
Dim aVals As Variant
Dim temp As Double
Worksheets(ChartName).ChartObjects(1).Activate 'assumes first series
With ActiveChart.SeriesCollection(1)
nPts = .points.Count 'save the number of points
aVals = .Values 'save all the values in array
For ipts = 1 To nPts 'loop through all points
temp = aVals(ipts) 'save value of specific point
if temp > 5 then
.points(iPts)).ApplyDataLabels AutoText:=True, _
LegendKey:=False, ShowSeriesName:=False, _ showCategoryName:=False, _
ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False
endif
next iPts
end with
Display More
I stumbled upon this while searching for icons to use for my custom macro toolbar. Its a site that contains all the FaceID values of all the icons you can use in Excel.
Hope someone else will find this as useful as me.
Hey
I think these custom shapes shouldn't be used for charts like the ones I'm making. Since my charts are so dynamic, its creating too many problems.
I'm going to try and figure out a better color coding system instead. Thanks for all the help.
Regards,
Problem
Okay, I managed to get my Points to display the custom markers.... NOW I have a bigger problem.
It seems once the points are set to custom, they cannot be converted back to a regular point.
Has anyone run into this problem before? I need to convert a custom marker back to a regular marker.
How can you specify the color Orange for the Interior color of a cell?
Iv'e tried with .Interior.Color = RGB(255, 153, 0)
This resulted in a Yellow color
Iv'e tried with .Interior.ColorIndex = 45 and 44
this resulted in a brownish color
Anyone else has had this problem?
-------------------------------
Shortly after posting, I have found the solution.
This will set the 45th color of the 56 color palette to Orange, the color it should have been in the first place.
Thanks
Thanks Andy,
I have solved the problem using your above mentioned suggestions. I would post my final code, although it is customized for my app, and it would be worthless for anyone else.
Anyways, Thanks again,
macro recorder
I recorded a macro to change a single Point with a custom shape, and this is what I got:
ActiveSheet.Shapes.AddShape(msoShape4pointStar, 261.75, 12.75, 20.25, 19.5).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 153, 0)
Selection.ShapeRange.Fill.Solid
Selection.Cut
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).points(9).Select
Selection.Paste
It somewhat does what I need, but it kinda looks messy, creating and deleting the shapes like that. I don't think I want to use this.
single point
Do you know if there would be a way to change a single point on the chart with a custom shape?
For example,
Worksheets(ChartName).ChartObjects(1).Activate
With ActiveChart.SeriesCollection(1)
.points(iPts).MarkerStyle = xlTriangle
Code was extremely trimmed, just to show. But basically, I go through my series one Point at a time, and check against various things. I need to be able to flag a single point as shown above.
Now, I understand MarkerStyle is just a integer representation of a shape, would there be any values that are not displayed that could be used ?
Easy way is not an options
This is not an option in my situation. I need to programatically create this marker since this workbook will be sitting on multiple PCs when i'm done with it.
My charts are all created dynamically, and there is no fix number of charts for each analysis that will be done using this workbook.
My main problem is, i need to flag so many thigns in the charts, that I'm running out of markers. (I'm working for chemists, they have quite a few things to check for in the charts)
If anyone can explain to me how to programatically insert a custom marker, this would be my goal. It doesn't matter if it is complicated, I have lots of time on my hands.
Can anyone tell me how to create a custom data point in a chart? My version of excel only allows me to use rectangles/triangles/circles/etc...
I was wondering if it would be possible to create my own shape, such as a "Plus sign" to incorporate in my charts.
If anyone can help on this subject, please do
Thanks in advance,