refedit query

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

How can I toggle between relative and absolute cell references using the F4
key after having selected a cell reference from a refedit control ?

I am unable to find the event in Refedit that will allow me to
use the F4 key. Please see Excel's Goal Seek Set cell: refedit control box,
where you can simply use the F4 key to toggle between all the absolute and
relative
cell references combinations. Any help you can provide will be very much
appreciated.

Rdgs,

Terry
 
Terry...


Try following:
the sendkeys is to cancel the normal refedit's reaction to f4...
i've included application.inputbox for demo only :)..


Option Explicit

Private Sub RefEdit1_KeyDown(KeyCode As Integer, ByVal Shift As Integer)
If KeyCode = vbKeyF4 Then
RefEdit1.Text = ToggleAbs(RefEdit1.Text)
SendKeys "~"
End If
End Sub


Private Sub UserForm_Initialize()
Dim r As Range
Set r = Application.InputBox("Demo application.inputbox", _
"Note the use of F4", Type:=8)
Me.RefEdit1 = r.Address(external:=True)
End Sub

Function ToggleAbs(sAddr$)
Dim relStyles, i%
relStyles = Array(xlRelative, xlAbsolute, xlAbsRowRelColumn, _
xlRelRowAbsColumn)
For i = 0 To 3
If sAddr = Application.ConvertFormula(sAddr, xlA1, xlA1, _
relStyles(i)) Then Exit For
Next
ToggleAbs = Application.ConvertFormula(sAddr, xlA1, xlA1, _
relStyles((i + 1) Mod 4))
End Function



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Tom appears to be a bug...

Pressing F4 in:

a refedit as in Application.inputbox works ok.(xl97/XP/2003)

a refedit in userform in xl97 works ok
a refedit in userform in xlXP AND xl2003
acts as ShowDropdown. (form is hidden, small "floater" appears.)
but does NOT toggle Abs/Rel



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Thanks keepitcool. Your suggestion solves the problem.

In your example, using the application.inputbox method, the screen does not
flash when you toggle the absolute/relative address with the F4 key,
resulting in a very smooth display. In the refedit control, you can see the
quick flashing of the screen when the SendKeys is used to close that floater
screen. Is there any way round this problem ?

Rdgs,

Terry
 
Tom and K I C

Just ran a test on my system with XL97 and XL2002 and the F4 key worked with
a RefEdit / InputBox in both versions. The possible difference in our
results may be the application of the hot fix in:

MSKB 291110
Xl2000: Cannot Use Keyboard Shortcuts To Select Ranges In Refedit Control

The Office/Excel service packs are supposed to have this fix in them, but it
appears somebody at MS went to lunch early.

Regards,
Jim Cone
San Francisco, CA
****************************
Tom Ogilvy said:
Same in xl2000, doesn't work. Excel 97 works fine - go figure.
Regards,
Tom Ogilvy
Click to expand...

-snip-
 
Seen James' post???

You need to add a registry setting in Excel\Options
Dword => QFE_Richmond=1

I've done it for all installed excel versions and it works!

Now the problems is.. you gotta make sure your users 've done it :)

I'm wondering why Microsoft published this as a fix for excel2000
and never bothered to solve it for Excel2002 or Excel 2003.

dah!


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


"Terry" <[email protected]> wrote:
 
Back
Top