Cakz Primz
Board Regular
- Joined
- Dec 4, 2016
- Messages
- 102
- Office Version
- 365
- Platform
- Windows
Dear folks,
I have a code that taken from internet to create/generate a skyline.
The data source is coming from "System" sheet and it will generate a skyline chart into "Skyline" sheet.
So far the code is working fine.
Below is the code that generating skyline:
But then, my boss wants to see skyline based on certain criteria on column C (RFSU) in "System" sheet.
And I do not know how to generate a dynamic skyline chart in VBA.
I wish and hope that anybody can help me.
Thank you very much in advance, for your kind attention and help.
Prima - Indonesia
Link:
Dynamic SH1 Skyline Overall 20230227 (CPY).xlsm
I have a code that taken from internet to create/generate a skyline.
The data source is coming from "System" sheet and it will generate a skyline chart into "Skyline" sheet.
So far the code is working fine.
Below is the code that generating skyline:
VBA Code:
Option Explicit
Option Private Module
Public skydate As Range
Public axisy As Integer
Public interval As Long
Public skyrange As Range
Public skycell As Range
Public plan As Range
Public y As Integer
Public fs As Integer
VBA Code:
Sub generate()
With Range("skylinearea")
.Clear
.Interior.Color = Range("skylineareabg").Interior.Color
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Color = Range("skylineareabg").Offset(0, 1).Interior.Color
End With
End With
On Error Resume Next
For Each skydate In Range("skylinedate")
interval = skydate - skydate.Offset(0, -1)
If Err.Number > 0 Then
interval = skydate.Offset(0, 1) - skydate
End If
Err.Clear
axisy = Range("skylinearea").Rows(Range("skylinearea").Rows.Count).Columns(1).Row
For y = 1 To Range("listplan").Rows.Count
Set plan = Range("listplan").Rows(y)
Set skycell = Sheet1.Cells(axisy, skydate.Column)
If ActiveSheet.Shapes("check2").ControlFormat.Value = 1 And Range("listcomp").Rows(y).Value = "OK" Then
If plan <= skydate And plan > (skydate - interval) Then
skycell = Range("listsystem").Rows(y)
axisy = axisy - 1
Call formatting
End If
End If
If Range("listcomp").Rows(y).Value <> "OK" Then
If plan <= skydate And plan > (skydate - interval) Then
skycell = Range("listsystem").Rows(y)
axisy = axisy - 1
Call formatting
End If
End If
Next
Next
Range("skylinearea").Select
End Sub
VBA Code:
Sub formatting()
fs = Range("fontsize").Value
With skycell
.Hyperlinks.Add anchor:=skycell, Address:="", SubAddress:=skycell.Address, ScreenTip:="Click for more detail"
.Interior.Color = Range(Range("liststatus").Rows(y)).Interior.Color
.Interior.Pattern = Range(Range("liststatus").Rows(y)).Interior.Pattern
.Interior.PatternColor = Range(Range("liststatus").Rows(y)).Interior.PatternColor
.Font.Color = Range(Range("liststatus").Rows(y)).Font.Color
.Font.Underline = xlUnderlineStyleNone
.Font.Bold = True
.Font.Size = fs
.WrapText = True
.HorizontalAlignment = xlCenter
With .Borders
.LineStyle = xlContinuous
.Color = Range(Range("liststatus").Rows(plan.Row - Range("liststatus").Row + 1)).Offset(0, 1).Interior.Color
End With
End With
End Sub
But then, my boss wants to see skyline based on certain criteria on column C (RFSU) in "System" sheet.
And I do not know how to generate a dynamic skyline chart in VBA.
I wish and hope that anybody can help me.
Thank you very much in advance, for your kind attention and help.
Prima - Indonesia
Link:
Dynamic SH1 Skyline Overall 20230227 (CPY).xlsm
Dynamic SH1 Skyline Overall 20230227 (CPY).xlsm | |||
---|---|---|---|
E | |||
236 | CCTV - Security Control Room - Offsite Area | ||
System |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
D5:E508 | Cell Value | duplicates | text | NO |