Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
refedit query
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
refedit query
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 "Terry" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
refedit query
I just hit F4 in a refedit box and it worked fine - did you try it?
xl97, SR2 -- Regards, Tom Ogilvy Terry wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
refedit query
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 "Tom Ogilvy" wrote: I just hit F4 in a refedit box and it worked fine - did you try it? xl97, SR2 -- Regards, Tom Ogilvy Terry wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
refedit query
Same in xl2000, doesn't work. Excel 97 works fine - go figure.
-- Regards, Tom Ogilvy keepitcool wrote in message ... 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 "Tom Ogilvy" wrote: I just hit F4 in a refedit box and it worked fine - did you try it? xl97, SR2 -- Regards, Tom Ogilvy Terry wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
refedit query
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 "keepitcool" wrote in message ... 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 "Terry" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
refedit query
Nope..
cant figure out how to disable the drop command to be sent to the refedit. sorry :( keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Terry" wrote: 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 "keepitcool" wrote in message ... 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 "Terry" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
refedit query
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" wrote: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RefEdit question | Excel Discussion (Misc queries) | |||
RefEdit control bug | Excel Programming | |||
RefEdit does not behave like Excel's built-in reference edit boxes | Excel Programming | |||
UserForm with RefEdit Modality Issue | Excel Programming |