Excel 2003 Macro help

[Fishnerd]

Can anyone tell me how to make a macro that when the graphic is clicked on,
the macro would select the cell that graphic is locked to (not by specific
name i.e. E5), move one cell to the right, and "double-click" that cell.

I need it to specifically select the cell that that specific graphic is in
and I will have hundreds of buttons doing this, and I want to be able to make
one and copy it where needed without all of them selecting the cell that the
original is in, and I don't want to do each and every one separately.

Thanks in advance for any help

 

[Jim Cone]

If your graphic is a Shape, then use the TopLeftCell property to select
the cell the Shape is over.
What is the reason or purpose for double-clicking the cell to the right?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Fishnerd"
wrote in message
Can anyone tell me how to make a macro that when the graphic is clicked on,
the macro would select the cell that graphic is locked to (not by specific
name i.e. E5), move one cell to the right, and "double-click" that cell.

I need it to specifically select the cell that that specific graphic is in
and I will have hundreds of buttons doing this, and I want to be able to make
one and copy it where needed without all of them selecting the cell that the
original is in, and I don't want to do each and every one separately.

Thanks in advance for any help

 

[Fishnerd]

Basically, Column D has buttons that look like miniture spanish flags.
Column E has a word in Spanish (without Wrap-text formatting active). Column
F has that same word in English. Column E's width is minimized so that the
Spanish word doesn't show. I'm trying to setup a macro on each flag button,
that when you press it, the cell to the right of that specific flag is
"double clicked" (as if you were double clicking it with the mouse), causing
just that cell with that spanish word to expand to it's full length, covering
up the English translation, and instead showing the word in Spanish.

Thanks Jim for giving me a hand with this! I've been trying to figure out
how to do it for a week now...

 

[Jim Cone]

A different approach...
Place your Spanish words in Column D. Place your English words in both Column E and F.
Hide Columns D and F, leaving all other columns showing.
Right click the sheet tab and choose "View Code"
Copy and paste the following code into the sheet module.
Double clicking a cell in Column C will then show the Spanish word in Column E.
Pressing enter or selecting another cell changes Spanish to English.
'------
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target, Me.Columns("C")) Is Nothing Then
Target(1, 3).Value = Target(1, 2).Value
Cancel = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Columns("E").Value = Me.Columns("F").Value
End Sub
------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Fishnerd"
wrote in message
Basically, Column D has buttons that look like miniture spanish flags.
Column E has a word in Spanish (without Wrap-text formatting active). Column
F has that same word in English. Column E's width is minimized so that the
Spanish word doesn't show. I'm trying to setup a macro on each flag button,
that when you press it, the cell to the right of that specific flag is
"double clicked" (as if you were double clicking it with the mouse), causing
just that cell with that spanish word to expand to it's full length, covering
up the English translation, and instead showing the word in Spanish.

Thanks Jim for giving me a hand with this! I've been trying to figure out
how to do it for a week now...

 

[Fishnerd]

NICE! I like it!
What do I do now to make clicking the miniature flags "double click" the
corresponding cell in Column C?

Thanks again Jim!

 

[Jim Cone]

My intention was to get rid of the shapes.
If you have 20,000 translations then you have to have 20,000 shapes.
That is not a good idea.
If you must then you can use this sub for all of the flag shapes...
'--
Sub What_Do_I_Do_Now()
Dim rng As Range
Set rng = ActiveSheet.Shapes(Application.Caller).TopLeftCell
rng(1, 3).Value = rng(1, 2).Value
Set rng = Nothing
End Sub
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Fishnerd"
wrote in message
NICE! I like it!
What do I do now to make clicking the miniature flags "double click" the
corresponding cell in Column C?
Thanks again Jim!

 

[Fishnerd]

Ahh.. okay!
Out of curiosity, why would it not be a good idea? Would it simply make the
file too huge? or would it take a long time to load the file? or would it
slow the computer down having to keep track of 20,000 shapes while within the
database? Is the problem having the 20,000 shapes? Or having 20,000 shapes
with macros?

I had figured (I gather incorrectly) that it would actually be less of a
load having the macro run only when a flag was clicked. It seemed when the
code is in the sheet module, as you showed me, the macro ran everytime I
moved the active cell regardless of where it was in the database. Is this a
problem? Is there a way to make it run only when the cell in Column C is
double clicked? Or is this not an issue, and I'm worrying needlessly?

Going with the first code you gave me, a few minor issues came up, that I
would be greatful if you could help me with:

1) It only works when the sheet is unprotected. I got around this by
unlocking Column E.. Is there a better way to do this?

2) I've got Column D (the spanish words) in blue, so it is apparent when the
translated spanish is being dispayed, but when the code displays the spanish
word in Column E it removes the color. I've tried adding the following to
the code, but I must be putting it in the wrong spot or something, cause it
isn't working:
With Selection.Font.ColorIndex = 5
End With

3) Instead of double clicking the cell in Column C, can it be changed to
just selecting that cell? I had initially been asking about double clicking,
cause that was the only way I knew of to get the spanish word to appear over
the english.

4) If I don't have a spanish translation in Column D yet, and I click C, it
empties the cell in Column E. I know the English word will appear back when
I select something else, but (though it seems counter-intuitive) I would the
english word to remain in this instance.

Sorry, for all the questions. The more I mess with this, the more
overwelmed, I feel. I'm struggling to figured this out, frustrated at the
spped I'm learning, and I can't tell you how much appreciate your help with
this!

Thanks again!

 

[Jim Cone]

RE: "Would it simply make the file too huge? or
would it take a long time to load the file? or
would it slow the computer down having to keep track of 20,000 shapes
while within the database? Is the problem having the 20,000 shapes? "

Answer: Yes

When writing a Excel program the core code is oft times the easy part.
The fun(?) comes with making something useful out of it.
My Find and List utility (finding and listing code portion) was done in a couple of hours.
It was 3 weeks later before it was considered usable.

You are entering the fun part. <g>
You might be able to find some sort of flag font or create one using
the windows character editor. (C:\WINDOWS\system32\eudcedit.exe)
That would eliminate the shapes.

Doing the rest of it is more fun than I want to have right now.
Others however may want to pitch in.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



in message
Ahh.. okay!
Out of curiosity, why would it not be a good idea? Would it simply make the
file too huge? or would it take a long time to load the file? or would it
slow the computer down having to keep track of 20,000 shapes while within the
database? Is the problem having the 20,000 shapes? Or having 20,000 shapes
with macros?

I had figured (I gather incorrectly) that it would actually be less of a
load having the macro run only when a flag was clicked. It seemed when the
code is in the sheet module, as you showed me, the macro ran everytime I
moved the active cell regardless of where it was in the database. Is this a
problem? Is there a way to make it run only when the cell in Column C is
double clicked? Or is this not an issue, and I'm worrying needlessly?

Going with the first code you gave me, a few minor issues came up, that I
would be greatful if you could help me with:

1) It only works when the sheet is unprotected. I got around this by
unlocking Column E.. Is there a better way to do this?

2) I've got Column D (the spanish words) in blue, so it is apparent when the
translated spanish is being dispayed, but when the code displays the spanish
word in Column E it removes the color. I've tried adding the following to
the code, but I must be putting it in the wrong spot or something, cause it
isn't working:
With Selection.Font.ColorIndex = 5
End With

3) Instead of double clicking the cell in Column C, can it be changed to
just selecting that cell? I had initially been asking about double clicking,
cause that was the only way I knew of to get the spanish word to appear over
the english.

4) If I don't have a spanish translation in Column D yet, and I click C, it
empties the cell in Column E. I know the English word will appear back when
I select something else, but (though it seems counter-intuitive) I would the
english word to remain in this instance.

Sorry, for all the questions. The more I mess with this, the more
overwelmed, I feel. I'm struggling to figured this out, frustrated at the
spped I'm learning, and I can't tell you how much appreciate your help with
this!

Thanks again!