ExcelBanter - Ran last Friday, doesn't this morning!

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Ran last Friday, doesn't this morning! (https://www.excelbanter.com/excel-programming/426799-ran-last-friday-doesnt-morning.html)

Ran last Friday, doesn't this morning!

I modified the code below last week to create a pick list in the sheet
using validation. I tested the program to see if it ran, and it ran fine.
So this morning I go to actully use it to create a new work tracking
worksheet, and I get an error on a part of the code I didn't touch last
week! The program is:

Sub NewWklySht()

Dim NewShtName As String
Dim bUserFin As Boolean

Set wsCurWklySht = ActiveSheet

If InStr(1, wsCurWklySht.Name, "Summary", vbTextCompare) 1 Then
MsgBox "Please select the Weekly Worksheet and restart this macro"
End
End If

NewShtName = Format(CDate(ActiveSheet.Name) + 7, "mmm dd")
Worksheets.Add(befo=Sheets("Yearly Activities")).Name = NewShtName
Set wsNewWklySht = Worksheets(NewShtName)

wsCurWklySht.Range("A1:C1").Copy
wsNewWklySht.Range("A1:C1").PasteSpecial Paste:=xlFormats,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

' Enter Month date in A1 and Admin in B1
wsNewWklySht.Range("A1").Value = NewShtName
wsNewWklySht.Range("B1").Value = "Admin"

'copy vlookup (cell C1)formula from old to new sheet
wsCurWklySht.Range("C1").Copy
wsNewWklySht.Range("C1").PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Range("A1").Select

'Opens Conditional Formatting dialog box for user input
Application.Dialolgs(xlDialogConditionalFormatting ).Show <--- OBJECT
DOESN'T SUPPORT THIS PROPERTY OR METHOD

'USER INPUT

Application.ScreenUpdating = False
Application.CutCopyMode = False
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C30"), Type:=xlFillDefault
Range("B1:B30").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=ActivityList"
End With

Call CopyColors1
Call formatPaint
Range("B:B").ColumnWidth = 25
Range("C:C").ColumnWidth = 15
Range("B2").Select
Application.ScreenUpdating = True
End Sub

This part of the program has been running fine for months and now won't.
Any ideas how adding the data validation routine below could interfere with
showing the Conditional Formatting dialog up above? Better yet, any
suggestions to fix it? I want the Conditional Formatting box to stay open
for the user to select the colors, then, when the user is done and clicks
Ok, the macro should continue.
Thanks!

Ran last Friday, doesn't this morning!

'Opens Conditional Formatting dialog box for user input
Application.Dialolgs(xlDialogConditionalFormatting ).Show <--- OBJECT
DOESN'T SUPPORT THIS PROPERTY OR METHOD

You spelled Dialogs incorrectly (no L in front of the G).

--
Rick (MVP - Excel)

"salgud" wrote in message
...
I modified the code below last week to create a pick list in the sheet
using validation. I tested the program to see if it ran, and it ran fine.
So this morning I go to actully use it to create a new work tracking
worksheet, and I get an error on a part of the code I didn't touch last
week! The program is:

Sub NewWklySht()

Dim NewShtName As String
Dim bUserFin As Boolean

Set wsCurWklySht = ActiveSheet

If InStr(1, wsCurWklySht.Name, "Summary", vbTextCompare) 1 Then
MsgBox "Please select the Weekly Worksheet and restart this macro"
End
End If

NewShtName = Format(CDate(ActiveSheet.Name) + 7, "mmm dd")
Worksheets.Add(befo=Sheets("Yearly Activities")).Name = NewShtName
Set wsNewWklySht = Worksheets(NewShtName)

wsCurWklySht.Range("A1:C1").Copy
wsNewWklySht.Range("A1:C1").PasteSpecial Paste:=xlFormats,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

' Enter Month date in A1 and Admin in B1
wsNewWklySht.Range("A1").Value = NewShtName
wsNewWklySht.Range("B1").Value = "Admin"

'copy vlookup (cell C1)formula from old to new sheet
wsCurWklySht.Range("C1").Copy
wsNewWklySht.Range("C1").PasteSpecial Paste:=xlFormulas,
Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Range("A1").Select

'Opens Conditional Formatting dialog box for user input
Application.Dialolgs(xlDialogConditionalFormatting ).Show <--- OBJECT
DOESN'T SUPPORT THIS PROPERTY OR METHOD

'USER INPUT

Application.ScreenUpdating = False
Application.CutCopyMode = False
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C30"), Type:=xlFillDefault
Range("B1:B30").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=ActivityList"
End With

Call CopyColors1
Call formatPaint
Range("B:B").ColumnWidth = 25
Range("C:C").ColumnWidth = 15
Range("B2").Select
Application.ScreenUpdating = True
End Sub

This part of the program has been running fine for months and now won't.
Any ideas how adding the data validation routine below could interfere
with
showing the Conditional Formatting dialog up above? Better yet, any
suggestions to fix it? I want the Conditional Formatting box to stay open
for the user to select the colors, then, when the user is done and clicks
Ok, the macro should continue.
Thanks!

Ran last Friday, doesn't this morning!

On Mon, 13 Apr 2009 17:41:57 +0100, Nigel wrote:

I suspected it never worked as there is a typo!

Try using.....

Application.Dialogs(xlDialogConditionalFormatting) .Show

Thanks to both of you. Actually, it did work. The typo got in this morning
when I meant to edit elsewhere and realized the cursor was not where I
thought it was. Thought I had fixed it. Doh!