invalid property assignment number

• 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

VBA Wrong number of arguments or invalid property assignment

• Thread starter Thread starter mark hansen
• Start date Start date Jun 1, 2020

mark hansen

Well-known member.

• Jun 1, 2020

I have a workbook that I use as a template to create other workbooks where the user collects information and sends that data to a data file. The main (starter) workbook is well used in many scenarios so I feel the VBA code is solid. The workbook was created so the only thing that need changing is the information that goes in the data string, the VBA code does not need changing. All the information the code needs (Path and File name for the data file, etc) is in a "Configuration" worksheet. so, I'm pretty sure the Worksheet_selectionsChange event is good, that's where the problem is showing up. To help the user enter data I use a Worksheet_SelectionsChange event to allow them to click on various cells to enter text, or bring up a pop up to pick the data they need. That's where the problems is now. When I click on cell I get the above error in the subject on a line that says If (Left(range("AL" & ActiveCell.Row).value,1) = "R" then the word "Left" is highlighted. OK Here are the changes that may have caused this. To aid with data collection, I have the user collecting data from our main frame through the Reflections Workspace program. I have code, running in Excel, that looks at the screen and captures information in certain places, and enters it into specific cells so it will be included in the data string. In order to get that to work, I needed to add the following reflections libraries ---- "Reflections for Unix and Open VMS" and "Reflections for Unix and Open VMS ActiveX Control 1.0 Type Library" Any ideas on what would cause the "Left" function to stop working? Thanks for any insight. Mark  

Excel Facts

MrExcel MVP, Moderator

It sounds like one of those libraries has its own Left function. Use VBA.Left instead.  

That was it!!! Thanks Rory  

Similar threads

• Question Question
• Oct 11, 2024
• May 30, 2024

• Fiddler1993
• Nov 5, 2024

• Ramadan2512
• Oct 19, 2024
• Mar 27, 2024

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

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Wrong number of arguments or property assignment not valid

• 8 contributors

An assignment has been attempted that is not valid.

To correct this error

Check that the number of arguments you have supplied matches the number required by the target.

Check the property assignment.

• Assignment Operators

Additional resources

Excel Top Contributors: HansV MVP  -  Andreas Killer  -  Ashish Mathur  -  Jim_ Gordon  -  Jeovany CV  ✅

November 12, 2024

Excel Top Contributors:

HansV MVP  -  Andreas Killer  -  Ashish Mathur  -  Jim_ Gordon  -  Jeovany CV  ✅

• Search the community and support articles
• Microsoft 365 and Office
• Search Community member

Ask a new question

I am getting a compile error: Wrong number of arguments or invalid property assignment

I cannot get the "format" to show up as "Format" in this particular workbook.  

TempFilePath = Environ$("temp") & "\"     TempFileName = "Part of " & Sourcewb.Name & " " _                  & f ormat (Now, "dd-mmm-yy h-mm-ss")

this path works in all my other workbooks and they are all saved as XLSM.

Any suggestions?

Report abuse

Reported content has been submitted​

Replies (14) 

* Please try a lower page number.

* Please enter only numbers.

• Independent Advisor

Was this reply helpful? Yes No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

Thanks for your feedback.

It has all of that already... here is what it looks like.  The email, subject, and body are not up to date.  The are in bold needs to be in caps but keeps moving back to lowercase after I change it.

Sub Mail_ActiveSheet() ' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010     Dim FileExtStr As String     Dim FileFormatNum As Long     Dim Sourcewb As Workbook     Dim Destwb As Workbook     Dim TempFilePath As String     Dim TempFileName As String     Dim OutApp As Object     Dim OutMail As Object     With Application         .ScreenUpdating = False         .EnableEvents = False     End With     Set Sourcewb = ActiveWorkbook     ' Next, copy the sheet to a new workbook.     ' You can also use the following line, instead of using the ActiveSheet object,    ' if you know the name of the sheet you want to mail :     ' Sheets("Sheet5").Copy     ActiveSheet.Copy     Set Destwb = ActiveWorkbook     ' Determine the Excel version, and file extension and format.     With Destwb         If Val(Application.Version) < 12 Then             ' For Excel 2000-2003             FileExtStr = ".xls": FileFormatNum = -4143         Else             ' For Excel 2007-2010, exit the subroutine if you answer             ' NO in the security dialog that is displayed when you copy             ' a sheet from an .xlsm file with macros disabled.             If Sourcewb.Name = .Name Then                 With Application                     .ScreenUpdating = True                     .EnableEvents = True                 End With                 MsgBox "You answered NO in the security dialog."                 Exit Sub             Else                 Select Case Sourcewb.FileFormat                 Case 51: FileExtStr = ".xlsx": FileFormatNum = 51                 Case 52:                     If .HasVBProject Then                         FileExtStr = ".xlsm": FileFormatNum = 52                     Else                         FileExtStr = ".xlsx": FileFormatNum = 51                     End If                 Case 56: FileExtStr = ".xls": FileFormatNum = 56                 Case Else: FileExtStr = ".xlsb": FileFormatNum = 50                 End Select             End If         End If     End With     ' You can use the following statements to change all cells in the    ' worksheet to values.     '    With Destwb.Sheets(1).UsedRange     '        .Cells.Copy     '        .Cells.PasteSpecial xlPasteValues     '        .Cells(1).Select     '    End With     '    Application.CutCopyMode = False     ' Save the new workbook, mail, and then delete it.     TempFilePath = Environ$("temp") & "\"     TempFileName = "Part of " & Sourcewb.Name & " " _                  & format( Now, "dd-mmm-yy h-mm-ss")     Set OutApp = CreateObject("Outlook.Application")          Set OutMail = OutApp.CreateItem(0)     With Destwb         .SaveAs TempFilePath & TempFileName & FileExtStr, _                 FileFormat:=FileFormatNum         On Error Resume Next        ' Change the mail address and subject in the macro before        ' running the procedure.         With OutMail             .To = "blank for now"             .CC = ""             .BCC = ""             .Subject = "This is the Subject line"             .Body = "Hello World!"             .Attachments.Add Destwb.FullName             ' You can add other files by uncommenting the following statement.             '.Attachments.Add ("C:\test.txt")             ' In place of the following statement, you can use ".Display" to             ' display the mail.             .Send         End With         On Error GoTo 0         .Close SaveChanges:=False     End With     ' Delete the file after sending.     Kill TempFilePath & TempFileName & FileExtStr     Set OutMail = Nothing     Set OutApp = Nothing     With Application         .ScreenUpdating = True         .EnableEvents = True     End With End Sub

Question Info

• Norsk Bokmål
• Ελληνικά
• Русский
• עברית
• العربية
• ไทย
• 한국어
• 中文(简体)
• 中文(繁體)
• 日本語