Trying to preserve BeforeDoubleClick Target and Pass to LostFocus

[Davmacrat]

Hi, Have cobbled together from different parts of the web a nifty snippet to deal with limitations of In Cell Data Validation Lists.

I'm using the Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) event to invoke a Flash Combo Box (Usually hidden away) to select a Date/Time Stamp (Real Number), Stored as Text by the Combo. Once the selection is made and I click out of the Combo, this invokes the Private Sub FlashCombo_LostFocus() event, shown below to convert the Combo output back into a real number using different variable types. The problem I have is trying to preserve the BeforeDoubleClick[/ICODE[B]][/B] [B]Target [/B]address, and getting it to [ICODE]LostFocus event so that the LostFocus code acts on the old Target rather than the new ActiceCell selection that triggered the LostFocus event. Do you know if this is possible? I've tried several ways but keep running into variable Type issues. Further to this, I've put a good Date/Time Stamp into the new cell I click on out of the Combo Box and have returned this value successfully via MsgBox. However, my selected Date/Time stamp is still being saved as Text. So there is still something not working.

The real number is derived from a named range, on a separate sheet, that is generated down the range from a

NOW()

function with successive fractions subtracted from the cell above down the range. This provides the user with a combo list of Date/Times going back approximately 2 hours to choose from and search. The receiving cell is custom formatted as dd/mm/yyyy hh:mm. There is another section of code similar to this which deals with pressing Enter or TAB to exit the combo Box and convert the output to the real number, and it works fine, but the code structure is different again. Have some self taught experience over the years (On and Off) but not getting my head round this one. Can you help with any advice as to how preserve the old active cell address for the LostFocus event? I think it is something quite simple, but not seeing it. I can send the whole set of Subs if you'd prefer.
Thanks very much
Dave.

Private Sub FlashCombo_LostFocus()

'Exit using Mouse
Dim LFE1BVV As Variant
Dim LFE1BTA As Variant

Set LFE1BTA = ActiveCell.Value '(THIS IS NOT GOING TO WORK! THE ACTIVE CELL IS NOW SOMEWHERE ELSE! NEED TO GET
        ' OLD TARGET ADDRESS FROM BeforeDoubleClick event)
MsgBox LFE1BTA

On Error Resume Next
  If Not LFE1BTA Is Nothing Then
  
     'change text to number

    If LFE1BTA.Value <> "" Then
      LFE1BVV = --LFE1BTA.Value
      If IsEmpty(LFE1BVV) Then
        LFE1BVV = LFE1BTA.Value
      End If
      LFE1BTA.Value = LFE1BVV
    End If
    Set LFE1BTA = Nothing
  End If
  
  'Put Combo away; Clear Out.

  With Me.FlashCombo
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End Sub

 

[Dan_W]

I'm not sure I fully follow, but assuming that the old target that you're referring to should be LFE1BTA, and that this is where the data is meant to be stored, then you need to change the variable's scope by repositioning it's declaration line to the top of the module (and outside of any procedures).

So, assuming that the LostFocus subroutine is in the same sheet module as the Worksheet_BeforeDoubleClick subroutine, then you would move this line to the top of the sheet module:

Dim LFE1BTA As Variant

Personally, I would use specific datatypes because it's not entirely clear what LFE1BTA is meant to be. At one point, you assigning it the value of Activecell (indicating that it should be a string, long, etc), nit at another point, you treat it as a Range object. I think you should clarify your data types because you will likely run into problems otherwise. This:

Set LFE1BTA = ActiveCell.Value

will more than likely throw a Type Mismatch error, for example.

Anyway, putting it all together, the following example will hopefully be useful:

Dim LFE1BTA As Range

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Set LFE1BTA = Target
End Sub

Private Sub ComboBox1_LostFocus()
    MsgBox LFE1BTA.Address
End Sub

 

[Davmacrat]

Dan, sorry for the tardy reply.
Thanks so much for the insight. Agree completely. My coding skills are a bit rusty and I forgot the concept of module-wide scope, mainly since it was a technique I always tried to avoid in the past. I will implement it and let you know how I get on.
Thanks again.
Dave.