userform - cell or column selection/reference

[buzz71023]

I need help with this portion of my spreadsheet and userform and no better place than here to get it.

I am wanting anytime any cell is selected in a given row (range myLEDGER, actually A19:AR71), a userform pops up (1^st code). I figure I need to Offset back to A somehow and then Offset back to the other ranges from there once I was in the userform to set the ranges)
When that form pops up I need it to show the values in the corresponding cells of the row that was selected.
Data is in the columns below

A (A and B are merged) (would = txtDate in userform)
C (C-E are merged) - (would = txtSTART in userform)
F (F-H are merged) - (would = txtEND in userform)
I (I and J are merged) - (would = txtHOURS in userform)
K (K-AH are merged) - (would = txtDETAILS in userform)
AO (AO-AP are merged) (would = txtCREW in userform)
AQ (AQ-AR are merged) - (would = txtINT in userform)

If the user changed anything and clicks the update button provided, I need those values to go back to the data ranges above.

Below (2^nd and 3^rd code) is what I have so far for opening the userform and executing the update command button) but it just isn’t working for me.

Hopefully this explains it well enough. Please feel free to ask any questions and I am open to any suggestions.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
If Not Application.Intersect(Target, Range("k19")) Is Nothing Then ' this will be myLEDGER but I have been trying with just one cell to get it to work
ActiveCell.Offset(0, -10).Activate
End If
 
End Sub

Private Sub UserForm_Initialize()
Dim DateRNG As Range, StartRNG As Range, EndRNG As Range, TypeHrRNG As Range, DetailRNG As Range, CrewRNG As Range, IntRNG As Range
 
txtDATE.Text = Format(txtDATE.Text, "dd mmm yyyy")
txtSTART.Text = Format(txtSTART.Text, "hh:mm AM/PM")
txtEND.Text = Format(txtEND.Text, "hh:mm AM/PM")
 
Set DateRNG = ActiveCell
Set StartRNG = ActiveCell.Offset(0, 2)
Set EndRNG = ActiveCell.Offset(0, 5)
Set TypeHrRNG = ActiveCell.Offset(0, 8)
Set DetailRNG = ActiveCell.Offset(0, 10)
Set CrewRNG = ActiveCell.Offset(0, 40)
Set IntRNG = ActiveCell.Offset(0, 42)
 
txtDATE = DateRNG.Value
txtSTART = StartRNG.Value
txtEND = EndRNG.Value
txtHOURS = TypeHrRNG.Value
txtDETAILS = DetailRNG.Value
txtCREW = CrewRNG.Value
txtINT = IntRNG.Value
 
End Sub

Private Sub CommandButton3_Click()
Dim DateRNG As Range, StartRNG As Range, EndRNG As Range, TypeHrRNG As Range, DetailRNG As Range, IntRNG As Range
Dim myRNG As Range
 
Set myRNG = ActiveCell
 
Set DateRNG = ActiveCell
Set StartRNG = ActiveCell.Offset(0, 2)
Set EndRNG = ActiveCell.Offset(0, 5)
Set TypeHrRNG = ActiveCell.Offset(0, 8)
Set DetailRNG = ActiveCell.Offset(0, 10)
Set CrewRNG = ActiveCell.Offset(0, 40)
Set IntRNG = ActiveCell.Offset(0, 42)
 
myRNG = txtDATE.Value
StartRNG = txtSTART.Value
EndRNG = txtEND.Value
TypeHrRNG = txtHOURS.Value
DetailRNG = txtDETAILS.Value
IntRNG = txtINT.Value
 
Unload me 
 
End Sub

BT

 

[sijpie]

There are several ways of achieving this. I often pass parameters (like in this case the row number of the selected cell) in the Tag property of the userform (or one of its controls, or even into a hidden control). Then i can do whatever I need to do with that row from the userform.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
' Assuming that the userform only needs to pop up when the row in fact holds some data:
    If Len(Target) Then
        With UserForm1
            .Tag = Target.Row
            .Show
        End With
    End If
 
End Sub

Then for the userform code:

Option Explicit

'Make these variables global to this module so they only need to be set once
Dim DateRNG As Range, StartRNG As Range, EndRNG As Range, TypeHrRNG As Range, DetailRNG As Range, CrewRNG As Range, IntRNG As Range
Dim r1stCell As Range

Private Sub UserForm_Initialize()
    
    'get the row number x from the tag and set the 1st cell to cell Ax
    Set r1stCell = ActiveSheet.Cells(Me.Tag, 1)
    
    'this does not work. You will need to set the format every time you enter something in the textbox
'    txtDATE.Text = Format(txtDATE.Text, "dd mmm yyyy")
'    txtSTART.Text = Format(txtSTART.Text, "hh:mm AM/PM")
'    txtEND.Text = Format(txtEND.Text, "hh:mm AM/PM")
     
    Set DateRNG = r1stCell
    Set StartRNG = r1stCell.Offset(0, 2)
    Set EndRNG = r1stCell.Offset(0, 5)
    Set TypeHrRNG = r1stCell.Offset(0, 8)
    Set DetailRNG = r1stCell.Offset(0, 10)
    Set CrewRNG = r1stCell.Offset(0, 40)
    Set IntRNG = r1stCell.Offset(0, 42)
     
    txtDATE = Format(DateRNG.Value, "dd mmm yyyy")
    txtSTART = Format(StartRNG.Value, "hh:mm AM/PM")
    txtEND = Format(EndRNG.Value, "hh:mm AM/PM")
    txtHOURS = TypeHrRNG.Value
    txtDETAILS = DetailRNG.Value
    txtCREW = CrewRNG.Value
    txtINT = IntRNG.Value
 
End Sub

'Code:

Private Sub CommandButton3_Click()
 
DateRNG = txtDATE.Value
StartRNG = txtSTART.Value
EndRNG = txtEND.Value
TypeHrRNG = txtHOURS.Value
DetailRNG = txtDETAILS.Value
IntRNG = txtINT.Value
 
Unload Me
 
End Sub

The whole thing can be done even more efficient, but this way you can still follow what is happening as it still is close to what you coded

 

[buzz71023]

I am having a problem.
Anytime I select any cell on the whole sheet this userform pops up.
Is there a way to define it to only open in a named range (which is "TimeLedger" or A19 to AQ1).

' Assuming that the userform only needs to pop up when the row in fact holds some data:
   
 If Len(Target) Then
        With frmTIME
            .Tag = Target.Row
            .Show
        End With
    End If
 
End Sub

 

[sijpie]

Yep, dead easy and standard. I was surprised you hadn't put something in already.

    If not intersect(target, range("TimeLedger") is Nothing then
        With frmTIME
            .Tag = Target.Row
            .Show
        End With
    End If

The above looks like a strange construction: The Intersect() function returns a range where two (or more) ranges intersect. So if the Target range has an intersect with the TimeLedger range, then the intersection will be returned. This means that the function does not return 'Nothing'. So if not is nothing then...