vba wrong number of arguments or invalid property assignment dictionary

• Mark Forums Read
• View Site Leaders
• Knowledgebase
• Consulting Services
• PayPal Donation
• Advanced Search
• VBA Code & Other Help
• [SOLVED:] Wrong number of arguments or invalid property assignments inserting a hyperlink
• If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Thread: Wrong number of arguments or invalid property assignments inserting a hyperlink

Thread tools.

• Show Printable Version
• View Profile
• View Forum Posts

Wrong number of arguments or invalid property assignments inserting a hyperlink

Hi all, please can someone give me a pointer, I'm self taught and usually scour forums to help me find answers to get my code to work but in this case I'm struggling. Everything I read says the code below should work but.... I'm using Excel to open word and generate a document from a series of strings stored in the excel file. One of my strings needs to have a hyperlink added. So I set the display text as disp from a value from excel. I then generate the hyperlink string by concatenating 2 fields as hyperadd (i'ts quite long). I then search for a bookmark "law" and select the 8 character word straight after it. Then I try to add the hyperlink at the selected txt and get "Wrong number of arguments or invalid property assignments". Any ideas? (bound to be something simple!) I suspect the issue is with the .range (and it not recognising my selection from the previous row but.....) Thanks If fieldtopass = "X9_0CX" Then Let disp = valuetopass If fieldtopass = "X9_0DX" Then Let hyperadd = valuetopass If fieldtopass = "X9_0EX" Then Let hyperadd = hyperadd & valuetopass With wrdDoc .Bookmarks("law").Select .Application.Selection.MoveRight Unit:=wdCharacter, Count:=8, Extend:=wdExtend .Application.Selection.Hyperlinks.Add Anchor:=Selection.Range, Address:=hyperadd, TextToDisplay:=disp End With End If

• Visit Homepage

The following works fine from within Word: Sub Test() Dim wrdDoc As Document Dim hyperadd As String, disp As String Set wrdDoc = ActiveDocument hyperadd = " www.google.com " disp = "Google" With wrdDoc .Bookmarks("law").Select Application.Selection.MoveRight Unit:=wdCharacter, Count:=8, Extend:=wdExtend Application.Selection.Hyperlinks.Add Anchor:=Selection.Range, Address:=hyperadd, TextToDisplay:=disp End With End Sub I suspect that you use of "Application" is referencing Excel and not Word. I don't have time to test in an Excel environment right now, but try replacing .Application with .Parent.

Greg Visit my website: http://gregmaxey.com

Hi Greg, yes I'm calling word from excel. I have tried .parent and it throws the same error. Thanks

Either of the following work calling Word from Excel. The issue is you are trying to use the Excel Application (and the Excel Selection) in a Word process: Option Explicit Sub ScratchMacroI() 'A basic Word macro coded by Greg Maxey, http://gregmaxey.com/word_tips.html , 9/17/2018 Dim oApp As Object Dim oDoc As Object Dim hyperadd As String, disp As String hyperadd = " www.google.com " disp = "Google" Set oApp = CreateObject("Word.Application") Set oDoc = oApp.Documents.Open("D:\Text.docx") With oDoc .Bookmarks("law").Select .Parent.Selection.MoveRight Unit:=1, Count:=8, Extend:=1 .Parent.Selection.Hyperlinks.Add Anchor:=.Parent.Selection.Range, Address:=hyperadd, TextToDisplay:=disp .Close -1 End With oApp.Quit Set oApp = Nothing lbl_Exit: Exit Sub End Sub Sub ScratchMacroII() 'A basic Word macro coded by Greg Maxey, http://gregmaxey.com/word_tips.html , 9/17/2018 Dim oApp As Object Dim oDoc As Object Dim hyperadd As String, disp As String hyperadd = " www.google.com " disp = "Google" Set oApp = CreateObject("Word.Application") Set oDoc = oApp.Documents.Open("D:\Text.docx") oDoc.Bookmarks("law").Select oApp.Selection.MoveRight Unit:=1, Count:=8, Extend:=1 oApp.Selection.Hyperlinks.Add Anchor:=oApp.Selection.Range, Address:=hyperadd, TextToDisplay:=disp oDoc.Close -1 oApp.Quit Set oApp = Nothing lbl_Exit: Exit Sub End Sub

Thanks Greg that worked, I just inserted " Add Anchor:=.Application.Selection.Range" into my code.

• Private Messages
• Subscriptions
• Who's Online
• Search Forums
• Forums Home
• Announcements
• Introductions
• How to Get Help
• Non English Help
• Access Help
• SUMPRODUCT And Other Array Functions
• Outlook Help
• PowerPoint Help
• Office 2007 Ribbon UI
• Integration/Automation of Office Applications Help
• Other Applications Help
• Project Assistance
• Testing Area
• Mac VBA Help
• Other Mac Issues
• Book Reviews

Tags for this Thread

View Tag Cloud

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
• BB code is On
• Smilies are On
• [IMG] code is On
• [VIDEO] code is On
• HTML code is Off

Forum Rules

• VBA Express
• Privacy Statement

• Forgotten Your Password?

• Mark Forums Read
• Quick Links :
• What's New?
• Members List

Forum Rules

• Commercial Services
• Advanced Search

• Microsoft Office Application Help - Excel Help forum
• Excel Programming / VBA / Macros

Wrong number of Arguments or Invalid property assignment

Thread tools.

• Show Printable Version
• Subscribe to this Thread…

Rate This Thread

• Current Rating
• ‎ Excellent
• ‎ Average
• ‎ Terrible

Hello - great tips on this website - giving me some great ideas - dont know whether I use them right yet - still knew to this. Below is my macro for taking a range of data in two columns, pivoting it, and replacing the original data with the pivoted data. I have had this working fine in a simpler form - but I am at present improving it to work on a variable data range!! Problem I have atm is that I get a "Wrong number of Arguments or Invalid property assignment" compile error whereas the same code previously worked fine - any ideaS?????? i have pointed out the point below where the problem starts!!! Sub Pivot_V5() ' ' Pivot_V5 Macro ' Macro recorded 17/07/2005 by Greg Sheriston ' ' ActiveWorkbook.Names.Add Name:="List", RefersToR1C1:= _ "=OFFSET(Sheet3!R1C1,4,0,COUNTA(Sheet3!C1),2)" ActiveWorkbook.Names.Add Name:="List2", RefersToR1C1:= _ "=OFFSET(Sheet3!R1C1,5,0,COUNTA(Sheet3!C1),2)" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="List"). _ CreatePivotTable TableDestination:="'[Pivot test.xls]Sheet3'!R6C5", _ TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="Stk no" ActiveSheet.PivotTables("PivotTable3").PivotFields("Val").Orientation = _ xlDataField ActiveWorkbook.ShowPivotTableFieldList = True Application.CommandBars("PivotTable").Visible = False ActiveWorkbook.ShowPivotTableFieldList = False Range("List2").Select <-----THIS BIT Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents Range("E8:F8").Select Range(Selection, Selection.End(xlDown)).Select Range("E8:F32").Select Selection.Copy Range("A6").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 " Columns("E:F").Select Selection.Delete Shift:=xlToLeft Range("A1").Select End Sub ANY ideas greatly appreciated - thanks in advance Greg

Re: Wrong number of Arguments or Invalid property assignment

Try Application.Goto Reference:="List2" -- Greetings from New Zealand Bill K "Turin" < [email protected] > wrote in message news: [email protected] ... > > Hello - great tips on this website - giving me some great ideas - dont > know whether I use them right yet - still knew to this. > > Below is my macro for taking a range of data in two columns, pivoting > it, and replacing the original data with the pivoted data. I have had > this working fine in a simpler form - but I am at present improving it > to work on a variable data range!! > > Problem I have atm is that I get a "Wrong number of Arguments or > Invalid property assignment" compile error whereas the same code > previously worked fine - any ideaS?????? i have pointed out the point > below where the problem starts!!! > > > Sub Pivot_V5() > ' > ' Pivot_V5 Macro > ' Macro recorded 17/07/2005 by Greg Sheriston > ' > > ' > ActiveWorkbook.Names.Add Name:="List", RefersToR1C1:= _ > "=OFFSET(Sheet3!R1C1,4,0,COUNTA(Sheet3!C1),2)" > > ActiveWorkbook.Names.Add Name:="List2", RefersToR1C1:= _ > "=OFFSET(Sheet3!R1C1,5,0,COUNTA(Sheet3!C1),2)" > > ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, > SourceData:="List"). _ > CreatePivotTable TableDestination:="'[Pivot > test.xls]Sheet3'!R6C5", _ > TableName:="PivotTable3", > DefaultVersion:=xlPivotTableVersion10 > ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="Stk > no" > > ActiveSheet.PivotTables("PivotTable3").PivotFields("Val").Orientation = > _ > xlDataField > ActiveWorkbook.ShowPivotTableFieldList = True > Application.CommandBars("PivotTable").Visible = False > ActiveWorkbook.ShowPivotTableFieldList = False > > > > Range("List2").Select <-----THIS BIT > Range(Selection, Selection.End(xlDown)).Select > Selection.ClearContents > > > > Range("E8:F8").Select > Range(Selection, Selection.End(xlDown)).Select > Range("E8:F32").Select > Selection.Copy > > > Range("A6").Select > ActiveSheet.Paste > Application.CutCopyMode = False > Selection.Sort Key1:=Range("B6"), Order1:=xlAscending, > Header:=xlNo, _ > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, > _ > DataOption1:=xlSortNormal > Selection.Borders(xlDiagonalDown).LineStyle = xlNone > Selection.Borders(xlDiagonalUp).LineStyle = xlNone > Selection.Borders(xlEdgeLeft).LineStyle = xlNone > Selection.Borders(xlEdgeTop).LineStyle = xlNone > Selection.Borders(xlEdgeBottom).LineStyle = xlNone > Selection.Borders(xlEdgeRight).LineStyle = xlNone > Selection.Borders(xlInsideVertical).LineStyle = xlNone > Selection.Borders(xlInsideHorizontal).LineStyle = xlNone > Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 " > Columns("E:F").Select > Selection.Delete Shift:=xlToLeft > > > > Range("A1").Select > End Sub > > > ANY ideas greatly appreciated - thanks in advance Greg > > > -- > Turin > ------------------------------------------------------------------------ > Turin's Profile: > http://www.excelforum.com/member.php...o&userid=24987 > View this thread: http://www.excelforum.com/showthread...hreadid=387864 >

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
• BB code is On
• Smilies are On
• [IMG] code is Off
• HTML code is Off
• Trackbacks are Off
• Pingbacks are Off
• Refbacks are Off
• ExcelForum.com

• Search forums
• Board Rules

Follow along with the video below to see how to install our site as a web app on your home screen.

Note: This feature may not be available in some browsers.

• If you would like to post, please check out the MrExcel Message Board FAQ and register here . If you forgot your password, you can reset your password .
• Question Forums
• Excel Questions

Debug wrong number of arguments or invalid property assignment error.

• Thread starter Harshil Mehta
• Start date Oct 6, 2020
• Tags #excel #vba

Harshil Mehta

Board regular.

• Oct 6, 2020

I have 7 sheets in total and want to make save as for the first 5 sheets with time stamp and password. The below code gives an error msg "wrong number of arguments or invalid property assignment" highlighting the word FORMAT. This code used to work perfectly before adding the the 7th sheet. The 7th sheet is a place where Raw Data is dumped and with the help of the VBA Code the data is populated into different sheets. Could anyone please help me? VBA Code: Sub Make_SaveAs() Dim ArrayOne() As String Dim Mypassword As String Application.ScreenUpdating = False Mypassword = Sheets(6).Range("E2") ReDim ArrayOne(1 To 5) For i = 1 To 5 ArrayOne(i) = Sheets(i).Name Next Worksheets(ArrayOne()).Copy With ActiveWorkbook .SaveAs Filename:=Environ("USERPROFILE") & "\Desktop\" & Format(Now(), "YYYYMMDD -") & " Client Design - " & ThisWorkbook.Sheets(6).Range("D2") & "_" & ThisWorkbook.Sheets(6).Range("C2") & ".xlsb", FileFormat:=xlExcel12, Password:=Mypassword .Close SaveChanges:=False End With Application.ScreenUpdating = True MsgBox "File Saved on Deskstop" End Sub  

Excel Facts

Well-known Member

Is it possible your desktop is connect to OneDrive? Look at the differences between these two msgboxes. VBA Code: MsgBox Environ("USERPROFILE") & "\Desktop\" MsgBox CreateObject("WScript.Shell").specialfolders("Desktop") & "\"  

davesexcel said: Is it possible your desktop is connect to OneDrive? Look at the differences between these two msgboxes. VBA Code: MsgBox Environ("USERPROFILE") & "\Desktop\" MsgBox CreateObject("WScript.Shell").specialfolders("Desktop") & "\" Click to expand...

So was there a difference in the msgboxes? Step through the code and see if the string variables do not contain illegal characters. VBA Code: Sub Make_SaveAs() Dim ArrayOne() As String Dim Mypassword As String Dim dr As String, dt As String, nm As String, fBM As String Dim sh As Worksheet Set WS = Sheet6 dr = CreateObject("WScript.Shell").specialfolders("Desktop") & "\" 'dr = "C:\Users\davem\OneDrive\Desktop\" dt = "Client Design - " & Format(Now(), "YYYYMMDD -") nm = WS.Range("D2").Value & "_" & WS.Range("C2").Value & ".xlsb" FNM = dr & dt & nm Application.ScreenUpdating = False Mypassword = WS.Range("E2") ReDim ArrayOne(1 To 5) For i = 1 To 5 ArrayOne(i) = Sheets(i).Name Next Worksheets(ArrayOne()).Copy With ActiveWorkbook .SaveAs Filename:=dr & dt & nm, FileFormat:=xlExcel12 ', Password:=Mypassword .Close SaveChanges:=False End With Application.ScreenUpdating = True MsgBox "File Saved on Deskstop" End Sub  

davesexcel said: So was there a difference in the msgboxes? Click to expand...

Okay. I was not getting a format error.  

davesexcel said: Okay. I was not getting a format error. Click to expand...

MrExcel MVP, Moderator

It sounds like you created a routine/variable called Format.  

Make sure that you do not have subs, functions or variables called Format  

Similar threads

• Nov 22, 2022
• OilEconomist
• Sunday at 8:18 PM

• Feb 9, 2024
• Aug 4, 2023

• Dec 13, 2021

Forum statistics

Share this page.

We've detected that you are using an adblocker.

Which adblocker are you using.

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock