Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 6th, 2006, 05:44 AM
Authorized User
 
Join Date: Sep 2006
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default run-time error '438' in excel 2003

I have a macro that works perfect in word 2003, but when i try to execute it in excel 2003 i always get the error run-time error '438'
(object doesn't support this property or method) why?

This is the macro:

Sub ABILIFY10()
    Selection.Font.Bold = True
    Selection.TypeText Text:="(ABILIFY 10 MG - CXS. 28 COMP.)"
    Selection.TypeParagraph
    Selection.TypeText Text:="Preço/comp.: "
    Selection.Font.Bold = False
    Selection.TypeText Text:=" CINCO VIRGULA ZERO TRÊS UM SETE OITO SEIS EUROS + IVA…………………………………."
    Selection.Font.Size = 6
    Selection.TypeParagraph
    Selection.TypeParagraph
    Selection.Font.Size = 12
    Selection.Font.Bold = False
    Selection.TypeText Text:="N.º Reg. INFARMED: 5056387"
    Selection.TypeParagraph
End Sub

Can someone help me, please.

 
Old October 6th, 2006, 08:40 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Properties TypeText, TypeParagraph are in Document Class, which is exclusive for Word. Excel does not have these properties

You can use the range properties to achieve the same. Here is your code in range

Sub ABILIFY10_ForExcel()

    Dim myTypeRange As Range

    Set myTypeRange = Cells(1, 2)
    myTypeRange.Font.Bold = True
    myTypeRange.Value = "(ABILIFY 10 MG - CXS. 28 COMP.)"
    Set myTypeRange = myTypeRange.Offset(1)
    myTypeRange.Value = "Preço/comp.: "
    myTypeRange.Font.Bold = False
    Set myTypeRange = myTypeRange.Offset(1)
    myTypeRange.Value = " CINCO VIRGULA ZERO TRÊS UM SETE OITO SEIS EUROS + IVA.............."
    myTypeRange.Font.Size = 6
    Set myTypeRange = myTypeRange.Offset(1)
    Set myTypeRange = myTypeRange.Offset(1)
    myTypeRange.Font.Size = 12
    myTypeRange.Font.Bold = False
    myTypeRange.Value = "N.º Reg. INFARMED: 5056387"
    Set myTypeRange = myTypeRange.Offset(1)
End Sub


Cheers
Shasur:)

http://www.vbadud.blogspot.com
 
Old October 9th, 2006, 04:09 AM
Authorized User
 
Join Date: Sep 2006
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi, it worked shasur, but it is possible to write the macro where i have my cursor (when i select a random cell) because if i want to select another cell to write the macro i would have to change the code in my macro.

Thank you.

 
Old October 9th, 2006, 04:14 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

That can be done

Replace Set myTypeRange = Cells(1, 2) with

Set myTypeRange = Activecell

This will do the needful

Cheers
Shasur

http://www.vbadud.blogspot.com
 
Old October 9th, 2006, 04:24 AM
Authorized User
 
Join Date: Sep 2006
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It worked.

You are the best, thanks

 
Old May 11th, 2009, 11:57 AM
Registered User
 
Join Date: May 2009
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default run-time error '438' in excel 2003

Hi,

I came across to this thread, which I found the nearest to my problem after searching on-line all day.

I understand that Selection.TypeParagraph is exlusive to Word document.
I have created tables in Word document from Excel, but when I use the above it doesn't work. I am trying to write not to Excel, but in Word table.

the macro works fine in Word.

Is there a workaround to this problem (writing to word tables as opposed to excel cells).

Thanks in advance.
 
Old May 11th, 2009, 08:16 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Quote:
Originally Posted by AlexTeslin View Post
Hi,

I came across to this thread, which I found the nearest to my problem after searching on-line all day.

I understand that Selection.TypeParagraph is exlusive to Word document.
I have created tables in Word document from Excel, but when I use the above it doesn't work. I am trying to write not to Excel, but in Word table.

the macro works fine in Word.

Is there a workaround to this problem (writing to word tables as opposed to excel cells).

Thanks in advance.
Hi Alex

Can you post the code and the line where you get error?

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old May 12th, 2009, 08:44 AM
Registered User
 
Join Date: May 2009
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default run-time error '438' in excel 2003

Hi Shasur,

thank you for your reply. I have managed to find way around by using range instead of selection. However, I still can't format individual words withing a cell:

My code with using selection is:
Code:
selection.Font.Size = 20
selection.Font.Color = 10108761
selection.TypeText Text:=candidateName
selection.Font.Size = 10
selection.TypeParagraph
selection.Font.Color = wdColorBlack
selection.TypeText Text:="Assigned Employment Advisor: "
This would print candidateName with given colour and size 20 and then print "Assigned Employment Advisor" string with size 10 and in black.

The code with range is:
Code:
With myCell.Range
        .Font.Size = 20
        .InsertAfter candidateName
        .Font.Size = 10
        .InsertParagraphAfter
        .InsertParagraphAfter
        .InsertAfter "Assigned Employment Advisor: "
        .InsertAfter "respUserName"
        .InsertParagraphAfter
        .InsertAfter "Registered on "
        .InsertAfter date1
        .InsertAfter " by "
        .InsertAfter createUserName
  End With
All above would be printed in size 10.

My question now is lightly different after using ranges.
Is there a way I can control individual words within a single cell?

Thank you
 
Old May 12th, 2009, 09:27 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Can you check this method

http://vbadud.blogspot.com/2007/09/e...r-part-of.html

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old May 13th, 2009, 05:08 AM
Registered User
 
Join Date: May 2009
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you Shasur,

I have not yet tried - will get back,

Cheers,





Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Query Error & Run-Time Error 3022 DavidWE Access 1 July 31st, 2008 11:17 AM
error 438 samuel2680 Excel VBA 1 January 2nd, 2008 12:49 PM
cant see button's image at run time vb.net 2003 nilesh_parmar General .NET 0 December 8th, 2005 10:06 PM
run time error ashishroyk Java GUI 0 October 8th, 2004 01:42 AM
RUN-TIME ERROR compcad Beginning VB 6 2 May 21st, 2004 02:01 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.