Add a value to a text box in user form based on the activecell

[2ks]

Evening

I am trying to get a text box to display a value (date) based on any cell that has been double clicked on from an excel sheet.

The user form loads up but I cannot get the txtbox to display the date from the activecell.

Thanks

 

[SkipVought]

Well, exactly WHAT does your code look like? My crystal ball is clouded over.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[2ks]

Many thanks

This is the coding in the Workbook that enables the double click in a cell to load user form:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

iSheet = ActiveSheet.Name
iColumn = ActiveCell.Column
irowno = ActiveCell.Row
icell = ActiveCell.Value

If iSheet = "Daily TEMPLATE" And iColumn = 2 And irowno > 8 And irowno < 841 Then
Cancel = True

frmFront.Show

End If

End Sub

Then the user form loads and this is my code:


Private Sub frmFront()

'Get row number
irow = ActiveCell.Row

'Where CELL_MIDATE is a global cons
txtDate.Value = Range(CELL_MIDATE & irow).Value

End Sub

 

[SkipVought]

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    
    If Sh.name = "Daily TEMPLATE" And Sh.Column = 2 And Sh.Row > 8 And Sh.Row < 841 Then
    Cancel = True
       
   frmFront.Show
         
    End If
    
End Sub
Private Sub frmFront()

    'Get row number
    irow = ActiveCell.Row
    
    'Where CELL_MIDATE is a global cons
    txtDate.Value = Range([b]CELL_MIDATE[/b] & irow).Value
    
End Sub

What is in CELL_MIDATE???

What function does frmFront have? Seems ABSOLUTELY NONE! You could have run this code from the DoubleClick Event as easily!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[2ks]

CELL_MIDATE is a global constant and is set to "B" to represent column B where the date will always be.

I tried frmFront_Initialize/Open/Activate to give it some purpose but usually had runobject problems especially with initialise.

 

[2ks]

This now brings the date into play as expected so many many thanks.

The date is reversed ie 2/1/2008 2nd Jan 08 is displayed as 1/2/08. I have experienced this before and do not really know why it occurs?

 

[SkipVought]

"The date is reversed ie 2/1/2008 2nd Jan 08 is displayed as 1/2/08. I have experienced this before and do not really know why it occurs? "

It's just a FORMAT display setting. faq68-5827.

"I tried frmFront_Initialize/Open/Activate to give it some purpose but usually had runobject problems especially with initialise."

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    
    If Sh.name = "Daily TEMPLATE" And Sh.Column = 2 And Sh.Row > 8 And Sh.Row < 841 Then
    Cancel = True
       
    with frmFront
      .txtDate.Value = Range(CELL_MIDATE & Sh.row).Value
      .Show
    end with
         
    End If
    
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[WalkerEvans]

2ks,

One of the things that can cause this is the "Locale (location)" setting under Format-Cells-Number. If this is set to "Enlish (United Kingdom)" vs "Enlish (United States)" you WILL get the UK date format, which is MM/DD/YY vs the DD/MM/YY format we're used to here in the United States.

Hope this helps.



----------------------------------------------------------------------------------
[small]"How many surrealists does it take to screw in a lightbulb?"[/small]
"Two -- one to hold the giraffe, the other to put the clocks in the bathtub."

 

[combo]

To avoid US date/number settings, don't link cells with VBA controls or pass values. This always returns US formats used in VBA. Instead set text:

.txtDate.Text = Format(Range(CELL_MIDATE & Sh.row).Value,"AnyDateFormat")

combo

 

[PHV]

I'd use this:
.txtDate.Text = Range(CELL_MIDATE & Sh.row).Text

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886