VB Syntax for Bypassing Dialog Boxes and Formatting Cells

Posted by Mark Israel on June 27, 2001 6:15 AM

I'm woring on an aout_open macro to import data from Access and create a report in Excel 97. I am almost done, but have three items I'd like to add to the code:

(1) Bypass or select the "Enable Macros" when opening the file. I do not want to permantently turn off macro virus protection.

(2) Once the data is imported, I need to delete the raw data from other workbooks. I want to bypass or select the "Delete Sheet" message.

(3) There is a column that has either red, yellow, or green as the text. I would like macro to search for cells with red and format them with red and so on for yellow and green.

Any assistance would be greatly appreciated.

Posted by Dax on June 27, 2001 8:13 AM

1. You can't bypass the Enable macros box unless you disable it completely. If code could execute before the workbook opened it would defeat the purpose of the box in the first place. You can get digital signature certificates but this is probably overkill.

2. You can stop the Delete sheet warning box from coming up by using the line Application.DisplayAlerts=False.

3. If it's just one column then this code should do it.

Sub ColourCells()
Dim cl As Range, RelevantRange As Range
Dim ColumnNumber As Integer, sht As Worksheet
Set sht = ActiveSheet
ColumnNumber = 4 'Change this to whatever column you need
Set RelevantRange = sht.Cells(1, ColumnNumber).Resize(sht.UsedRange.Rows.Count)
For Each cl In RelevantRange.Cells
Select Case cl.Value
Case "Red"
cl.Font.Color = vbRed
Case "Yellow"
cl.Font.Color = vbYellow
Case "Green"
cl.Font.Color = vbGreen
End Select
Next
End Sub

HTH,
Dax.

Posted by Mark Israel on June 27, 2001 10:36 AM

Thanks for the help!!!!