|
Excel2000でVBAを組んだファイルで1年程問題なく
使えていたのですが、急にコマンドボタンがひとつ消え、
またひとつ消える症状が発生し、困っています。
開いたファイルを閉じる際にコマンドボタンが消えます。
また、コマンドボタンは見えなくなるのではなく完全に
消去されてしまいます。
どうしていいのか、全く手に負えません。
何かアドバイスを頂ければ幸いです。
Private Sub workbook_open()
Dim lnglCnt As Long
Dim Sh As Worksheet
For lnglCnt = 1 To Application.CommandBars.Count
Application.CommandBars(lnglCnt).Enabled = False
Next lnglCnt
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
ActiveSheet.Unprotect
Sheets("Sheet1").Activate
On Error Resume Next
ActiveSheet.Unprotect
If ActiveSheet.AutoFilterMode = False Then
Range("$b$4:$df$4").AutoFilter
End If
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True
For Each Sh In Worksheets
Sh.EnableAutoFilter = True
Sh.Protect UserInterfaceOnly:=True
Next Sh
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lnglCnt As Long
For lnglCnt = 1 To Application.CommandBars.Count
Application.CommandBars(lnglCnt).Enabled = True
Next lnglCnt
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
Sheets("Sheet1").Activate
ActiveSheet.Unprotect
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
With Application
Range("$D$5:$D$3000").Value = .Asc(.Trim(.Clean(Range("$D$5:$D$3000"))))
Range("$CH$5:$CH$3000").Value = .Asc(.Trim(.Clean(Range("$CH$5:$CH$3000"))))
Range("$CI$5:$CI$3000").Value = .Asc(.Trim(.Clean(Range("$CI$5:$CI$3000"))))
Range("$CJ$5:$CJ$3000").Value = .Asc(.Trim(.Clean(Range("$CJ$5:$CJ$3000"))))
Range("$CK$5:$CK$3000").Value = .Asc(.Trim(.Clean(Range("$CK$5:$CK$3000"))))
Range("$CL$5:$CL$3000").Value = .Asc(.Trim(.Clean(Range("$CL$5:$CL$3000"))))
Range("$CM$5:$CM$3000").Value = .Asc(.Trim(.Clean(Range("$CM$5:$CM$3000"))))
Range("$CN$5:$CN$3000").Value = .Asc(.Trim(.Clean(Range("$CN$5:$CN$3000"))))
Range("$CO$5:$CO$3000").Value = .Asc(.Trim(.Clean(Range("$CO$5:$CO$3000"))))
Range("$CP$5:$CP$3000").Value = .Asc(.Trim(.Clean(Range("$CP$5:$CP$3000"))))
Range("$CQ$5:$CQ$3000").Value = .Asc(.Trim(.Clean(Range("$CQ$5:$CQ$3000"))))
Range("$CR$5:$CR$3000").Value = .Asc(.Trim(.Clean(Range("$CR$5:$CR$3000"))))
Range("$CW$5:$CW$3000").Value = .Asc(.Trim(.Clean(Range("$CW$5:$CW$3000"))))
Range("$CX$5:$CX$3000").Value = .Asc(.Trim(.Clean(Range("$CX$5:$CX$3000"))))
Range("$DF$5:$DF$3000").Value = .Asc(.Trim(.Clean(Range("$DF$5:$DF$3000"))))
End With
ActiveSheet.Protect
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
If Me.Saved = False Then Me.Save
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Dim lnglCnt As Long
For lnglCnt = 1 To Application.CommandBars.Count
Application.CommandBars(lnglCnt).Enabled = False
Next lnglCnt
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Dim lnglCnt As Long
For lnglCnt = 1 To Application.CommandBars.Count
Application.CommandBars(lnglCnt).Enabled = True
Next lnglCnt
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
End Sub
|
|