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

davidmyers

Board Regular
Joined
Jan 29, 2017
Messages
74
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,169
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?

Code:
    [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

Board Regular
Joined
Jan 29, 2017
Messages
74
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
Code:
If Me.IDNumb.Value <> Cells(ActiveCell.Row, 2) Then
      msgbox "wrong ID number"
End If
Is that correct?

Thanks
David
 

davidmyers

Board Regular
Joined
Jan 29, 2017
Messages
74
Hi Where would I put this code to limit it's use to only when the UserForm was open?

Thanks
David
 

davidmyers

Board Regular
Joined
Jan 29, 2017
Messages
74
Here's my solution after googling some more
Code:
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
 

Forum statistics

Threads
1,082,576
Messages
5,366,419
Members
400,888
Latest member
Cdim7

Some videos you may like

This Week's Hot Topics

Top