active cell wont populate text box on form

[Rob Mann]

Please could someone look at the code below and tell me why the text box "txtjobnum" wont populate with the active cell in my "COMPLAINTS" sheet. The green code below is in the userform and the red text below that is in module 3. Any ideas?



Private Sub cancel_Click()
Unload Me
End Sub

Private Sub continue_click()
ActiveWorkbook.Sheets("COMPLAINTS").Activate
ActiveCell.Offset(0, 7) = recontactdate.Value
ActiveCell.Offset(0, 8) = recollectionreqd.Value
ActiveCell.Offset(0, 9) = note.Value
Unload Me
End Sub

Private Sub UserForm_initialize()
Me.txtjobnum = ActiveCell.Value
recontactdate.Value = Format(Date, "dd/mm/yy")
With recollectionreqd
.AddItem "Yes"
.AddItem "No"
End With
note.Value = ""
End Sub

Sub Find_Last1()
Dim FindString As String
Dim Rng As Range
FindString = InputBox("Enter a job number")
ActiveWorkbook.Sheets("COMPLAINTS").Visible = True
If Trim(FindString) <> "" Then
With Sheets("COMPLAINTS").Range("C:C")
Set Rng = .Find(What:=FindString, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
ActiveWorkbook.Sheets("COMPLAINTS").Visible = False
ActiveWorkbook.Sheets("OPTIONS").Select
EditEntry.Show
Else
MsgBox "Nothing found"
End If
End With
End If

End Sub

 

[Trevor G]

The useform is activated but you haven't stated which sheet or cell it is to activate to?

if it had

sheets("The Name").Activate.Range("Cell ID").Activate

Then it would work. Change the sheet name and cell ID to the ones you want to work with

Private Sub UserForm_initialize()
sheets("The Name").Activate.Range("Cell ID").Activate
Me.txtjobnum = ActiveCell.Value

 

[Weaver]

I created a test form using more or less the code you have and it worked fine, without having to explicitly reference the sheet and the cell (I think doing this defeats the object in any case)

Are there any other objects, like shapes or charts on the sheet that you might have selected when you open the form?