Replies

I am encountering trouble with color palettes on many workbooks. I was advised to use the Reset button on the color options. It does nothing. The defect causes a dark blue/gray background on many rows and cells, making black text almost invisible. I’ve tried this many times and the Reset approach never works. Thanks for any advice.

Ok, thanks. I’ll try this asap.

The problem could occur if the user’s Personal.xls workbook contains a worksheet named Journal, or if the user tries to open any workbook that contains a hidden sheet named Journal.

making progress! we have Excel opening up now with a blank worksheet, but the macro now runs every time, instead of stopping when it cannot find a sheet named Journal. Also, how do I delete the Class11 object? I think it might be gumming things up.

I checked and rechecked and I have exactly what works in my workbook, which is a copy of what was originally suggested. The Excel I am trying to get this on opens up without any workbook or worksheet open. Is that why I get “Method ‘Range of object’_Global failed”? The macro seems to execute automatically until the statement Range (“e18”).Select is reached, then it stops. I stop the debugger, open a blank worksheet, then open a file that should trigger the FixedAsset macro. Is it too late by then? Thanks.

Sorry this took so long..here’s the code i have on someone else’s personal xls to run the test. It works on mine but I get that error msg on this uint. Lines have been temporairly rem’d out to avoid problemsThanks.
Class1:
‘Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
‘Dim ws As Worksheet
‘ Dim lngErr As Long
‘ On Error Resume Next
‘ Set ws = Wb.Worksheets(“Journal”)
‘ If there is no worksheet named Journal,
‘ lngErr will be unequal to 0
‘ lngErr = Err.Number
‘ On Error GoTo 0
‘ If Not lngErr = 0 Then

‘ Code to correct problem here?
‘ Else
‘ Wb.Worksheets(“Journal”).Select

‘ FixedAssets
‘ Sheet exists
‘ End If
‘End Sub

In Module 1:
Sub FixedAssets()
Dim msg As Variant
Dim NaturalCell As Range
Range(“e18”).Select
Range(ActiveCell, “e2000”).Select

For Each NaturalCell In Selection
If NaturalCell.Value > 160000 And NaturalCell.Value <= 180000 Then
With NaturalCell
.Interior.Color = vbRed
.Font.Color = vbWhite
End With
msg = True
End If
Next NaturalCell
If msg = True Then
MsgBox "Fixed Assets Involved!!!"
Else
MsgBox "No Fixed Asset accounts found"
End If
End Sub

And in the This Workbook Module/Workbook Open
Private Sub Workbook_Open()
Set myClass.app = Application

End Sub

Hope this is enough. I'll keep trying.

Where does the error 461 occur? It means that something doesn’t exist.

Also, since you haven’t given us the code for FixedAssets, it’s impossible for us to tell why it fails.

Ok, let me get that to you shortly. thanks.

I got the setup to work on my pc. I copied each module, and the class module, into someone else’s Personal.xls and added the This Workbook statement to their personal xls file. When I closed Excel and restarted it, and opened a test file, I got an Error 461 message, which I cannot interpret. I carefully reviewed the macros etc. and they look ok. When I inserted a Class Module, it created one called Class1 and then I imported the class module with the code in it, renaming it Class11. I copied it into the CLass1 module, but could not delete the other one. How does one do that? Also, with this stuff in the Personal Xls file, when it calls FixedAssets, that macro suddenly bombs because it says it cannot reset the interionr color to red. Never happened before. Ideas? I’ll keep trying. Thanks!!

Remove the line that only contains “End”:

Code:

...
	FixedAssets
	' Sheet exists
	End ' ************ remove this line ***********
  End If
End Sub

This line clears all variables!

Looks like it! Will test with others now. Does that “bad statement” affect just that section or the Class1Module and the other module as well?

Are you sure that you copied each of the parts into the correct module? I’ve tested the code, and it worked for EACH workbook that was opened.

I put the Public myClass As New Class1 in a Module named Woodys_763868.
In the Personal xls “ThisWorkbook” module I have the following:
Sub ChangeTitlebar()
ActiveWindow.Caption = ActiveWorkbook.FullName

End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName & “/” & ” &A” & vbCr
ActiveSheet.PageSetup.RightFooter = “&D &T”
ActiveSheet.PageSetup.CenterFooter = “&P of &N”

End Sub

Private Sub Workbook_Open()
Set myClass.app = Application
End Sub

And finally, I have the piece de resistance in the Class1 module:

Public WithEvents app As Application

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
Dim ws As Worksheet
Dim lngErr As Long
On Error Resume Next
Set ws = Wb.Worksheets(“Journal”)
‘ If there is no worksheet named Journal,
‘ lngErr will be unequal to 0
lngErr = Err.Number
On Error GoTo 0
If Not lngErr = 0 Then

‘ Code to correct problem here?
Else
Wb.Worksheets(“Journal”).Select

FixedAssets
‘ Sheet exists
End
End If
End Sub

It happend again today-predictably-that it worked on the first workbook I opened but not the second. I basically was flipping the call to the FixedAssets test sub until I got it to work at all, but it may be in the wrong spot. Thanks!

The code could look like this:

In the class module:

Code:

Public WithEvents app As Application

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
  Dim ws As Worksheet
  Dim lngErr As Long
  On Error Resume Next
  Set ws = Wb.Worksheets("Journal")
  ' If there is no worksheet named Journal,
  ' lngErr will be unequal to 0
  lngErr = Err.Number
  On Error GoTo 0
  If Not lngErr = 0 Then
	' Code to correct problem here?
  Else
	' Sheet exists
  End If
End Sub

At the top of a standard module:

Code:

Public myClass As New Class1

where Class1 is the name of the class module.

In the ThisWorkbook module:

Code:

Private Sub Workbook_Open()
  Set myClass.app = Application
End Sub

The latter procedure is called each time the workbook is opened. It initializes the app variable and thereby gets the ball rolling.

Ok, I copied the above into my Personal.xls and fiddled with it until it worked on the first workbook I opened that had a Jorunal tab with the account number range in it. When I tried a second workbook, nothing happened. The macro should check each workbook that gets opened in Excel. The processors open dozens a day and any one may or may not have an account to be checked. But we’re making progress! Thanks.

This is brilliant. I go to the Microsoft Article and try to print it…the bulleted data doesn’t print, even when I select the Print this page button. Any idea why?

I read the article and now want to have the macro test to see if there is a tab “Journal” in the workbook, to address the issue cited, and if so, run the macro, if not, end. So how do I frame that test? How does one say in activeworkbook.sheets.names??? includes “Journal” then do the sub, else end? One other question: the article seemed to say you have to run the Test sub to get the workbook open event macro to run. Is that true? Thanks.

Active.Workbook.Range(“b12”) isn’t well-defined. Apart from the fact that it’s ActiveWorkbook, not Active.Workbook, what if the workbook has more than one worksheet? What if there is a worksheet with “Journal Description” in cell B12, but that sheet is not the active one?
Your macro will need to be modified depending on the answers.

Furthermore, in order to use application-level events, you need to do a little bit more. See How to create application-level event handlers in Excel and Application Events.

This is brilliant. I go to the Microsoft Article and try to print it…the bulleted data doesn’t print, even when I select the Print this page button. Any idea why?

The easiest way to toggle protection on and off is to click the “lock” button on the Forms toolbar. This has the added advantage that it preserves the content of already filled-in form fields.

Ok, i put up the toolbar; it is handy. But when I run spellcheck with protection off, I am told everything is OK. Yet I see obvious errors, even when I select the whole document and F7 it again.

Thanks! The form was not password protected and I am able to spell check. Will keep the macro for future use. However, now that I have run Spellcheck, and I want to add some text to the boxes, when I try and click the boxes, the whole box is selected and I cannot get into the text to change things. What a nuisance. I hesitate to reinstate Protection because I cannot interpret what the checkboxes would do.

You could create a custom function in the Visual Basic Editor:

Code:

Function TestFormula(oCell As Range) As Boolean
  If oCell.HasFormula Then
	If InStr(oCell.Formula, "*12") > 0 Then
	  TestFormula = True
	End If
  End If
End Function

Let’s say you have formulas in column A. In another cell, for example in B1, enter the formula

=TestFormula(A1)

and fill down.

Alternatively, use

=IF(TestFormula(A1),”Uh oh!”, “”)

That’s great, thanks! The Alternative is more accurate evaluation.