Reset the macro assigned to Ctrl+F on workbook deactivate

[swtupr]

I have an excel workbook where i have assigned an excel macro for the short
cut Ctrl+F as below:

Sub WorkBook_Activate()
Application.OnKey "^f", "FindByValue"
Application.OnKey "^F", "FindByValue"
End Sub

Public Sub FindByValue()
Application.Dialogs(xlDialogFormulaFind).Show,2,2 // to find by value not
formula
End Sub

And then i am trying to reset the ctrl+f functionality to normal in the
deactivate event as below:

sub WorkBook_Deactivate()
Application.OnKey "^f", "FindByValue"
Application.OnKey "^F", "FindByValue"
End Sub

The Ctrl+F working fine in my workbook where i have this code. But when i
open some other excel sheet and try to do a Ctrl+F the find dialog box is not
opened and nothing is happening .

Can i know why ctrl+f not working in other excel sheets and can i get a
solution for this issue please?????


Thanks.

 

[Jim Cone]

This...
sub WorkBook_Deactivate()
Application.OnKey "^f", "FindByValue"
Application.OnKey "^F", "FindByValue"
End Sub

Should be...
sub WorkBook_Deactivate()
Application.OnKey "^f"
Application.OnKey "^F"
End Sub
--
Jim Cone
Portland, Oregon USA
(what is that little white box at the top right corner of the vbe?)




"swtupr"
wrote in message
I have an excel workbook where i have assigned an excel macro for the short
cut Ctrl+F as below:

Sub WorkBook_Activate()
Application.OnKey "^f", "FindByValue"
Application.OnKey "^F", "FindByValue"
End Sub

Public Sub FindByValue()
Application.Dialogs(xlDialogFormulaFind).Show,2,2 // to find by value not
formula
End Sub

And then i am trying to reset the ctrl+f functionality to normal in the
deactivate event as below:

sub WorkBook_Deactivate()
Application.OnKey "^f", "FindByValue"
Application.OnKey "^F", "FindByValue"
End Sub

The Ctrl+F working fine in my workbook where i have this code. But when i
open some other excel sheet and try to do a Ctrl+F the find dialog box is not
opened and nothing is happening .
Can i know why ctrl+f not working in other excel sheets and can i get a
solution for this issue please?????
Thanks.

 

[swtupr]

Hi Jim,
Thanks for your reply.
Sorry there was a mistake when i pasted the code here.
Actually there is no "FindByValue" in my deactivate event. I gave the
exactly same way as you mentioned

Application.Onkey "^f"

But i am still facing the above mentioned problem. Am not able to figure out
wats wrong...

 

[Jim Cone]

Re: "when i open some other excel sheet"

Do you mean "when I switch to another sheet" and not
"when I open another workbook" ?
If so your code belongs in the Sheet module of each sheet where
you want OnKey to work. The code would go in these subs ...

Private Sub Worksheet_Activate()
Private Sub Worksheet_Deactivate()
--
Jim Cone
Portland, Oregon USA



"swtupr"
wrote in message
Hi Jim,
Thanks for your reply.
Sorry there was a mistake when i pasted the code here.
Actually there is no "FindByValue" in my deactivate event. I gave the
exactly same way as you mentioned
Application.Onkey "^f"
But i am still facing the above mentioned problem. Am not able to figure out
wats wrong...

 

[swtupr]

Ctrl+f doesnt work when i open another new workbook not a new sheet in the
same workbook where i have the code. I have the code in my ThisWorkBook
module not in Sheet module.Hope i am clear now....

 

[Jim Cone]

Looks like you will have to use this ...
'--
Sub WorkBook_Deactivate()
Application.CommandBars(1).Controls("Edit").Controls("Find...").Reset
End Sub
'--
It appears OnKey won't restore menu commands just normal keyboard strokes.
--
Jim Cone
Portland, Oregon USA




"swtupr"
wrote in message
Ctrl+f doesnt work when i open another new workbook not a new sheet
in the same workbook where i have the code.
I have the code in my ThisWorkBook module not in Sheet module.
Hope i am clear now....

 

[swtupr]

Thanks a lot for you help Jim but could you please be more clear on the
statement
Application.commandbars..........
as i am getting an error at this statement
Application.CommandBars(1).Controls("Find").Reset

Any idea about this.

Thanks.

Looks like you will have to use this ...
'--
Sub WorkBook_Deactivate()
Application.CommandBars(1).Controls("Edit").Controls("Find...").Reset
End Sub
'--
It appears OnKey won't restore menu commands just normal keyboard strokes.
--
Jim Cone
Portland, Oregon USA




"swtupr"
wrote in message
Ctrl+f doesnt work when i open another new workbook not a new sheet
in the same workbook where i have the code.
I have the code in my ThisWorkBook module not in Sheet module.
Hope i am clear now....

 

[swtupr]

And one more thing i would like to be specific is that i am working with
EXCEL 2007.

Thanks.

Thanks a lot for you help Jim but could you please be more clear on the
statement
Application.commandbars..........
as i am getting an error at this statement
Application.CommandBars(1).Controls("Find").Reset

Any idea about this.

Thanks.

 

[Jim Cone]

The code I gave you was not what you tried, I posted ...
Application.CommandBars(1).Controls("Edit").Controls("Find...").Reset
Also, the OnKey statements would also have to be run in conjunction
with the code above.

However, I don't have XL2007; XL2007 doesn't have Menubars
and the XL2007 ribbon does not respond to VBA, so wasted effort all around.
--
Jim Cone
Portland, Oregon USA




"swtupr"
wrote in message
And one more thing i would like to be specific is that i am working with
EXCEL 2007.
Thanks.

Thanks a lot for you help Jim but could you please be more clear on the
statement
Application.commandbars..........
as i am getting an error at this statement
Application.CommandBars(1).Controls("Find").Reset

Any idea about this.

Thanks.

 

[swtupr]

In Excel 2007 VBA i could not get the statement yuo have posted here
Application.CommandBars(1).Controls("Edit").controls("Find...").Reset gives
me an error.

If XL2007 ribbon does not respond to VBA, Is there no way where i can reset
the Shortcut Ctrl+F to normal in workbook_Deactivate()?????

 

[Jim Cone]

Rod de Bruin has lots of stuff on the Ribbon.
Looks like you should start here...
http://www.rondebruin.nl/acceleratorskeys.htm
--
Jim Cone
Portland, Oregon USA




"swtupr"
wrote in message
In Excel 2007 VBA i could not get the statement yuo have posted here
Application.CommandBars(1).Controls("Edit").controls("Find...").Reset gives
me an error.
If XL2007 ribbon does not respond to VBA, Is there no way where i can
reset the Shortcut Ctrl+F to normal in workbook_Deactivate()?????

 

[swtupr]

Thanks so much for your help Jim. I will go through that site.