copy/past from cell to modeless userform - how to ensure cell is within a range

[davidmyers]

Hi, I have a modeless UserForm and want to copy/paste from cells on the sheet to a textbox on the userform. How do I find activecell to ensure I'm copying from a valid range?

Thanks for any help
David

 

[AlphaFrog]

Hi, I have a modeless UserForm and want to copy/paste from cells on the sheet to a textbox on the userform. How do I find activecell to ensure I'm copying from a valid range?

Thanks for any help
David

This will copy from the ActiveCell if it is within range B10:D20. Is that what you're asking?

    [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Range("B10:D20"), ActiveCell) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        TextBox1.Text = ActiveCell.Text
    [color=darkblue]Else[/color]
        MsgBox "The active cell is out of range."
    [color=darkblue]End[/color] [color=darkblue]If[/color]

 

[davidmyers]

Thanks AlphaFrog,
Yes this is what I'm looking for, my situation is I have a group of (contiguous) rows all with the same id number. I want to check that the cell that was clicked is on a row with the same id number as the cell where I opened the user form - I have the id number on the userform so I need something like

If Me.IDNumb.Value <> Cells(ActiveCell.Row, 2) Then
      msgbox "wrong ID number"
End If

Is that correct?

Thanks
David

 

[davidmyers]

Hi Where would I put this code to limit it's use to only when the UserForm was open?

Thanks
David

 

[davidmyers]

Here's my solution after googling some more

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lRow As Long
Dim diff As Integer
lRow = Cells(Rows.Count, 2).End(xlUp).Row
If Selection.Count = 1 Then
        If Not Intersect(Target, Range("E2:E" & lRow)) Is Nothing Then
            UserForm3.Show
        End If
        If Not Intersect(Target, Range("F2:F" & lRow)) Is Nothing Then
            UserForm3.Show
        End If
    End If
    If IsUserFormLoaded("UserForm4") Then
        diff = UserForm4.SpeciesID.Value - Cells(ActiveCell.Row, 2).Value
        If diff = 0 Then
             UserForm4.TextBox1.text = ActiveCell.text
        Else
            MsgBox " Selected field from wrong species"
        End If
    End If
End Sub


Function IsUserFormLoaded(ByVal UFName As String) As Boolean
  Dim UForm As Object
  IsUserFormLoaded = False
  For Each UForm In VBA.UserForms
    If UForm.Name = UFName Then
      IsUserFormLoaded = True
      Exit For
    End If
  Next
End Function

The "If diff = 0 " part is because " if UserForm4.SpeciesID = Cells(ActiveCell.Row, 2) " didn't work - always evaluated to not equal

Thanks for your help
David