Object Required Error when setting value

duncanGLP · #1 (**permalink**) 06-23-10, 15:04

Hi Guys,

I am pretty new to Visual Basic and I'm trying to use a macro assigned to a button in excel that will enter the current date in a cell if another cell shows a value of 1 (or 100% complete); if its not got a value of 1 (i.e. not 100% complete) I want it to show a message box to say so.

The spreadsheet I am using is not finalised so I want to use defined names instead of cell references:

Code:


Sub Completed_Emplymnt_Dtls_Manager_Date()
'
' Record date that Manager completes Employment & Address details

'Set a to Cell value for cell showing completion amount
Dim a As String
Set a = Range("Completed_Emplymnt_Dtls").Value

'Check if is 100% completed
If a = 1 Then
'Enter current date into Date Completed cell
    Application.Goto Reference:="Completed_Emplymnt_Dtls_Manager_Date"
    ActiveCell.FormulaR1C1 = Format(Now(), "mm-dd-yyyy")

Else
'Or show error message
    MsgBox "This section is not yet complete", vbExclamation, "Incomplete"
    
End If
'
End Sub

When I run the macro it shows an "Object Required" error on

Set a = Range("Completed_Emplymnt_Dtls").Value

Can anyone help me? I'm going to have to repeat this code for quite a large number of buttons throughout several workbooks, so I would really appreciate it!

Thanks

Duncan

p.s. I have tried googling this and nothing I have tried works - you guys are my last hope before it drives me crazy!

Colin Legg · #2 (**permalink**) 06-23-10, 15:52

Hi,

You should be using the Let keyword instead of Set. Let is optional in VBA and most people choose just to drop it:

Code:

Dim a As String

Let a = Range("Completed_Emplymnt_Dtls").Value

Most people write as:

Code:

Dim a As String

a = Range("Completed_Emplymnt_Dtls").Value

I haven't checked the rest of your code so let us know if you hit any more problems...

Hope that helps,

duncanGLP · #3 (**permalink**) 06-23-10, 16:03

Thanks Colin - I'd been racking my brains for ages and had tried a number of different methods but to no avail...

Works a charm anyway,

Thanks again

Duncan

Internet Services

Web Development

Digital Marketing

Consumer Tech