Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Question Unanswered: Object Required Error when setting value

    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!

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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,

  3. #3
    Join Date
    Jun 2010
    Posts
    2
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •