Excel VBA - Error 438 Object doesn't support this property or method

[kazzy]

Hi VBAers,

In the code below, I am getting an error on the following line:
ElseIf ireply = vbNo And ActiveWorkbook = filename Then


Sub FN_CreateDOHVSRBatch()

Dim filename As String
Dim ireply As Integer

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set wb1 = ActiveWorkbook
filename = Worksheets("Amtreference").Range("A2").Text

Batchprompt:
ireply = MsgBox(Prompt:="Do you wish to create a new batch?", _
Buttons:=vbYesNo, Title:="Create New Service Request
Batch")

If ireply = vbYes Then
ActiveWorkbook.SaveAs filename:=filename & Format(Date,
"yyyymmdd ") _
& Format(Time, "hh.mm") & ".xls"

ElseIf ireply = vbNo And ActiveWorkbook = filename Then
MsgBox "You cannot input data into the actual DOHV Service
Request Template. Please respond 'Yes' to create new batch."
GoTo Batchprompt

ElseIf ireply = vbNo And ActiveWorkbook <> filename Then
Exit Sub

End If

End Sub


Obviously I cannot put "and activeworkbook = filename then with the
'if' statement" but i have no idea how else to code this.

Can anyone please help?

 

[kazzy]

Hi VBAers,

In the code below, I am getting an error on the following line:
 ElseIf ireply = vbNo And ActiveWorkbook = filename Then

Sub FN_CreateDOHVSRBatch()

    Dim filename As String
    Dim ireply As Integer

Application.DisplayAlerts = False
Application.ScreenUpdating = False

 '   Set wb1 = ActiveWorkbook
    filename = Worksheets("Amtreference").Range("A2").Text

Batchprompt:
    ireply = MsgBox(Prompt:="Do you wish to create a new batch?",_
            Buttons:=vbYesNo, Title:="Create New Service Request
Batch")

    If ireply = vbYes Then
         ActiveWorkbook.SaveAs filename:=filename & Format(Date,
"yyyymmdd ") _
         & Format(Time, "hh.mm") & ".xls"

    ElseIf ireply = vbNo And ActiveWorkbook = filename Then
          MsgBox "You cannot input data into the actual DOHV Service
Request Template. Please respond 'Yes' to create new batch."
        GoTo Batchprompt

    ElseIf ireply = vbNo And ActiveWorkbook <> filename Then
        Exit Sub

    End If

End Sub

Obviously I cannot put "and activeworkbook = filename then with the
'if' statement" but i have no idea how else to code this.

Can anyone please help?

I worked it out. I had to put .Name on the appropriate lines,
like this.

ElseIf ireply = vbNo And ActiveWorkbook.Name = filename Then

 

[GS]

kazzy used his keyboard to write :

I worked it out. I had to put .Name on the appropriate lines,
like this.

ElseIf ireply = vbNo And ActiveWorkbook.Name = filename Then

Actually, since there's no other executable code following the If..Then
construst then there's no need for the ElseIf; The flow goes directly
to End Sub.

 

[kazzy]

kazzy used his keyboard to write :








Actually, since there's no other executable code following the If..Then
construst then there's no need for the ElseIf; The flow goes directly
to End Sub.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Very much appreciate the advcie Garry. I will do.

 

[rodrigorpinto]

Hi Everybody! This is my first post here and I am keen in leraning more and share my expertise as well....

My question probably is quite easy but I spent all day yesterday working on it trying to find a solution but with no success!

This is where I am having problems:

Dim cld_edt, country as String
Dim obj_cld as Object
etc....
etc....

Set obj_cld = ThisWorkbook.VBProject.VBComponents(cld_edt)
With obj_cld.Properties
.Caption = CStr(country) & " - Campaign Period"
End With

Basicaly the code sets a new object (already created) via a string (cld_edt) and then try to change the caption.

The message error I am receiving as an answer is "Method 'Properties' of object '_VBComponent' failed"

I think that the set is not an issue, but if I try for example to show the form I receive the same error...

Anyone can help me??

Thanks a lot in advance! Hope to share knowledge here!

Rodrigo