Search code examples
excelvbauserform

Refedit to return corresponding row and column

Can someone help me with retrieving the corresponding row and column when a range is selected via Refedit? I put the pictures below how it looks like.

What I want to do is when I select a range (for example E12:E16) via the "Refedit1" in my userform, it should return the start and end time for the corresponding row (11AM - 3PM +1) and the corresponding date in column (wednesday 26/02/2020) Next step would be to insert these values immediately inside the 3 DTPickers but this I can do once I have the return values, I think.

I tried all different codes that I found and they always give me either the value that is in the cell ("" in this example), a text string like "sheet1$E$12 or when I use Active.Cell it returns the cell that was active before I selected my range through RefEdit.

Hopefully someone can point me in the right direction, I would help me a lot! Sorry that I couldn`t upload the original excel file but there was to many confidential info in it...

sheet layout

enter image description here

Userform layout

enter image description here

Private Sub CommandButton2_Click()
 Dim rRange As Range
 Dim strAddr As String
 Dim bIsRange As Boolean
          'Get the address, or reference, from the RefEdit control.
          strAddr = RefEdit1.Value


          'Use IsObject to find out if the string is a valid address.
          On Error Resume Next
          bIsRange = IsObject(Range(strAddr))
          On Error GoTo 0

          If bIsRange = False Then 'Not Valid
            MsgBox "The range is not valid"
            RefEdit1.Value = vbNullString
            RefEdit1.SetFocus
            Exit Sub
          End If

          'Set the rRange Range variable to the range nominated by the
          'RefEdit control. If the Sheet name is also include (eg Sheet2!A1:A10)
          'It will act on that range, even if the sheet is not active at the time.
          Set rRange = Range(strAddr)

          ' gives the cell reference as a string
          MsgBox strAddr

                   With rRange
                '.Interior.ColorIndex = 16
                .Font.Bold = True
                '.BorderAround LineStyle:=xlContinuous, Weight:=xlThick
           End With
        If strAddr = "" Then
        'do nothing
        Else
        Range(strAddr).Value = UserForm1.ComboBox2.Value
        End If



End Sub
Solution

  • You can read the date and times like this:

    With rRange
    '.Interior.ColorIndex = 16
    .Font.Bold = True
    '.BorderAround LineStyle:=xlContinuous, Weight:=xlThick

    startTime = .cells(1).EntireRow.cells(2).Value

    endTime = .cells(.cells.count).EntireRow.cells(2).Value

    theDate = .cells(1).EntireColumn.cells(5).Value

End With