maaf mas @Caton...saya sudah gunakan dua cara yg diberikan mas caton...yg pertama
Solusinya, sisipkan satu baris pada baris ke-1 pada sheet INDUK2 tersebut
dan juga
Solusinya, hapus formula pada range yang belum terpakai (kosong) tersebut
dan yang kedua saya coba ke file lain pakai scriptnya yang diatas
Public Sub CheckMaxIndex(Validate As Boolean)
If ([AL1] = [AL2] - 1) Or Validate Then
Dim xlShape As Shape
Dim lRow As Long
On Error Resume Next
Set xlShape = Me.Shapes("spnIndex")
With xlShape.ControlFormat
If ([AL2] * 1) Then
.Max = [AL2]
Else
'lRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
lRow = Application.CountIf(Sheet2.Range("A4:A1000"), ">0")
If lRow = 0 Then
lRow = Application.CountIf(Sheet2.Range("A4:A1000"), "?*")
End If
.Max = IIf(lRow > 0, lRow, 100)
End If
End With
Err.Clear
On Error GoTo 0
End If
End Sub
untuk ke sheet induk2 tidak ada masalah tapi tetap saja script yg di sheet1 (sekolah) dibawah ini
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [E20:E21]) Is Nothing Then
Dim lIdx As Long
Dim xAddr
On Error Resume Next
Application.ScreenUpdating = False
If Target.Address = [E20].Address Then
lIdx = Application.Match(Val(Target), [{1, 2, 3, 4, 5, 6, 7, 8 , 9}], 0)
Else
lIdx = Val([E20])
End If
If Err.Number = 0 Then
'+-- Sheet Kompetensi Dasar (KD).
With Sheet17
xAddr = [{"A:R", "S:AJ", "AK:BD", "BE:CB", "CC:CZ", "DA:DX", "DY:EU", "EV:FR", "FS:GO"}]
.Columns("A:GO").Hidden = True
.Columns(xAddr(lIdx)).Hidden = False
End With
'+-- Sheet Raport.
With Sheet8
'+-- Sembunyikan baris!
.Rows(27).Hidden = IIf(lIdx < 3, True, False) '+-- SKI
.Rows(35).Hidden = IIf(lIdx < 3, True, False) '+-- IPA
.Rows(36).Hidden = IIf(lIdx < 3, True, False) '+-- IPS
.Rows(38).Hidden = IIf(lIdx < 7, True, False) '+-- Bahasa Inggris
'+-- Tinggi baris!
.Rows(26).RowHeight = IIf(lIdx < 3, 165, 87.75)
.Rows(32).RowHeight = IIf(lIdx < 3, 165, 87.75)
.Rows(33).RowHeight = IIf(lIdx < 3, 165, 87.75)
.Rows(37).RowHeight = IIf(lIdx < 7, 165, 87.75)
End With
'+-- Hide beberapa kolom sekaligus!
If InStr(1, "GANJIL|GENAP", UCase$([E21])) > 0 Then
With Sheet5
'+-- Kolom semester!
.Columns("A:BR").Hidden = False
.Columns("B:AI").Hidden = (UCase$([E21]) = "GENAP")
.Columns("AJ:BQ").Hidden = (UCase$([E21]) = "GANJIL")
'+-- Kolom per kelas per semester!
.Range("H:H, L:M, X:X, AB:AC").EntireColumn.Hidden = (lIdx < 3) '+-- Ganjil.
.Range("AP:AP, AT:AU, BF:BF, BJ:BK").EntireColumn.Hidden = (lIdx < 3) '+-- Genap.
.Range("P:P, AF:AF, AX:AX, BN:BN").EntireColumn.Hidden = (lIdx < 7)
.Activate
.Range(IIf(UCase$([E21]) = "GANJIL", "C10", "AK10")).Activate
End With
End If
Me.Activate
End If
Application.ScreenUpdating = True
Err.Clear
On Error GoTo 0
End If
End Sub
tidak bisa jalan ke sheet yang ditargetkan dalam hal ini sheet5 dan sheet8.. mohon solusinya mas @caton