Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 26th 03, 04:27 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 4
Default 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   Report Post  
Old October 26th 03, 05:28 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,253
Default 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   Report Post  
Old October 26th 03, 05:41 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 27,285
Default 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   Report Post  
Old October 26th 03, 10:49 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,253
Default 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   Report Post  
Old October 27th 03, 12:00 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 27,285
Default 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   Report Post  
Old October 27th 03, 03:27 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 4
Default 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   Report Post  
Old October 27th 03, 04:27 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,253
Default 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   Report Post  
Old October 27th 03, 04:40 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,253
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
RefEdit question tt Excel Discussion (Misc queries) 2 February 8th 06 03:42 AM
RefEdit control bug Dag Johansen[_6_] Excel Programming 1 October 20th 03 12:32 PM
RefEdit does not behave like Excel's built-in reference edit boxes Tom Ogilvy Excel Programming 0 September 11th 03 06:43 PM
UserForm with RefEdit Modality Issue Tom Ogilvy Excel Programming 0 August 20th 03 12:32 AM


All times are GMT +1. The time now is 03:40 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017