VBA Error when I activate another sheet

Question

I am working on a sheet for my work but I'm stuck on something that I can't solve by searching on the internet.

I've got 2 sheets that I'm using at the moment.

Sheet 1: A database with all the information; Sheet 2: A list with data taken out of Sheet1 and an option to add extra information by using a UserForm1.

On Sheet2 I'm using a hyperlink to start up the UserForm1 with this code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Column = "4" Then
        UserForm1.show
        Exit Sub
    End If
End Sub

This worked good until I started working with code on the UserForm1. I made a Vlookup in the VBA UserForm1 with target on Sheet1. Now as soon as I click the hyperlink I get an error and the debug refers to the above code "UserForm1".

The UserForm1 Code:

Private Sub UserForm_Initialize()
  Object = ActiveCell
  TextBox1.Value = Application.WorksheetFunction.VLookup(Object, Sheet1.Range("$B$2:$BW$622"), 1, False)
  Label3 = ActiveCell.Offset(0, 1).Value
  Label5 = ActiveCell.Offset(0, 3).Value
End Sub

Private Sub BUT_Annuleren_Click()
  Unload Me
End Sub

The same happens when I use "Sheets("Sheet1").activate". Can someone help me with this to take information in my Userform1 out of multiple sheets?

Thanks for the help!

Ron

xiaodai xiaodai · Accepted Answer · 2013-06-10T06:19:38

Try this, don't forget to use the Set keyword when assigning objects.

Private Sub UserForm_Initialize()
    Set Object = ActiveCell
    TextBox1.Value = Application.WorksheetFunction.VLookup(Object, Sheet1.Range("$B$2:$BW$622"), 1, False)
    Label3 = ActiveCell.Offset(0, 1).Value
    Label5 = ActiveCell.Offset(0, 3).Value
End Sub

Private Sub BUT_Annuleren_Click()
    Unload Me
End Sub

VBA Error when I activate another sheet

1 Answers