RefEdit updating Current Cell location

[Guest]

Hello,
How to prompt a user to select only 1 cell-location type using RefEdit when
a form is running?
For example:

I have a form with 1 RefEdit (RefEdit1) and 2 TextBoxes
(txtCellCurrentLocation and txtSheetCurrentLocation)

1. When the form start running it will get the current cell location and it
will show the results on txtCellCurrentLocation and txtSheetCurrentLocation

2. After that, the RefEdit tool can be clicked to select another new current
location,

*Also RefEdit1 value has to be only 1 cell located only in Columns C, E and
G.
*If the user select a range of cells it will get a message to try again and
select only 1 cell or quit form.

3. After getting a new good value for RefEdit1 then txtCellCurrentLocation
and txtSheetCurrentLocation textboxes will be updated in the form.

=================

Private Sub RefEdit1_BeforeDragOver(Cancel As Boolean, ByVal Data As
MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As
stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As
MSForms.fmDropEffect, ByVal Shift As Integer)

End Sub

Private Sub TxtCellCurrentLocation_Change()

End Sub

Private Sub TxtSheetCurrentLocation_Change()

End Sub

Private Sub UserForm_Initialize()
TxtSheetCurrentLocation = ActiveSheet.Name
TxtCellCurrentLocation = ActiveCell.Address
End Sub
============


Thanks

Coco

 

[Jon Peltier]

The RefEdit events are notoriously unreliable, and RefEdits in general
are very shaky. I crashed Excel twice just trying to do the events for this.

Finally I remembered that I don't use these events. What I usually do is
use a separate button for the user to click, like the "Validate" button
that links to this code:

Private Sub btnValidate_Click()
' Validate RefEdit selection
Dim r As Range
Dim s As String
' Resize range to one cell
Set r = Range(Me.RefEdit1.Value).Resize(1, 1)
If Not r Is Nothing Then
' select cell
r.Select
s = r.Address(external:=True)
Me.RefEdit1.Value = Mid(s, InStr(s, "]") + 1)
Select Case r.Column
' which column is cell in
Case 3, 5, 7
Case Else
' wrong column
Me.RefEdit1.Value = ""
Me.RefEdit1.SetFocus
MsgBox "Please select a cell in columns C, E, or G", _
vbExclamation
End Select
End If
End Sub

The other option is to capture the event when any other control receives
focus, and run the procedure above.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______