Excel VBA command button

[peterd51]

Hi,

I can create a button in Excel and add a macro to it...

next I want to have 'up' and 'down' arrows that the user can click on and it'll toggle a search on that column, then replace the existing arrow with the other one for next time.

I can do the search OK, it's changing the arrow that has me beaten.

I tried using the 'arrow-lines' but it's messy as it adds a new 'line' number each time it's written and I can't delete the old one as I don't know the number.

So now I have two small .jpgs and I can load them up into the selected cell with, for instance...

Range("B1").Select
ActiveSheet.Pictures.Insert ("C:\up.jpg")

But I can't see how to delete a single picture when I load up the 'other' one, I can only delete /all/ pictures on a sheet with ActiveSheet.Pictures.Delete.

Also, how could I position the pictures to the right of the cell allowing for a title on the left?

Or is there a better way of doing this please?

Regards
Peter

 

[Loomah]

hi
first bit - you can refer to the picture by name or index so activesheet.pictures(1).delete will work as will activesheet.pictures("mypicture").delete

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[peterd51]

Hi,

thanks for that.

Imagine a spreadsheet with seven 'up' arrows,one at the top of each column of data. They would have been loaded in order and if the user click on column 3 I can run the sort, I guess then I'd delete picture 3 and load the down arrow into that column.

I guess this is now picture 8?

Without messsing around and saving that info, how would I know which picture to delete next time the user clicks on that column?

I could simply over-write the old picture with the new one but it's not very clever.

That's only part of the problem though...I really would like to position the picture on the right hand side of each column and I can't see a way of specifying where it's going to load with what I've got so far.

If there's a 'nice' way to do it I'd like to give it a go.

Regards
Peter

 

[Loomah]

here's something to play with

Sub ject()
Dim sSize As Single
With Range("A1")
    .Select
    .Parent.Pictures.Insert( _
        "C:\Program Files\Microsoft Office\MEDIA\CAGCAT10\j0183290.wmf").Select
End With
With Selection.ShapeRange
    sSize = Range("A1").Height
    .Height = sSize
    .Width = sSize
    .IncrementLeft Range("A1").Width - .Width
    .IncrementTop 2
    .Name = "Picture1"
End With
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

ok, i didn't see your last post before i sent mine!

name your pictures as i've shown - don't use my naming convention use something like up_col1, down_col1, up_col2 etc.

i can't rteally picture how you are going to fire an event to change the pictures though as i'm sure you'll lose any code association when a picture is deleted. i'm guessing you'd need the selection change event and hope your users will click on the cell rather than the picture.

having said that i think it is possible to assign code to shapes 'on the fly' but i've never done it.

have a little play with what i've given you and post back!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

and to assign procedure on the fly

ActiveSheet.Shapes("Picture1").OnAction = "sort"

gotta love the macro recorder so often!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[combo]

Maybe those hints will be useful:
- you can have hidden template images,
- instead of 'Copy' use 'Duplicate', it will return copied object,
- the 'TopLeftCell' and 'BottomRightCell' properties can be useful to find location of shape,
- Application.Caller returns name of clicked shape.

combo

 

[peterd51]

Hi,

Nice one, thanks!

this is what I've come up with...

Sub Macro1()

  Dim sSize As Single
  With Range("H1")
    .Select
    .Parent.Pictures.Insert("C:\up.jpg").Select

  End With

  With Selection.ShapeRange
    sSize = Range("H1").Height
    .Height = sSize
    .Width = sSize
    .IncrementLeft Range("H1").Width - .Width
    .IncrementTop 2
    .Name = "Picture557"
  End With

  Selection.OnAction = "Macro101"

End Sub

Sub Macro101()
  Dim xfer1 As String

  xfer1 = Application.Caller

  Range("H1").Select
  ActiveSheet.Pictures(xfer1).Delete
  
  Dim sSize As Single
  With Range("H1")
    .Select
    .Parent.Pictures.Insert("C:\dn.jpg").Select

  End With

  With Selection.ShapeRange
    sSize = Range("H1").Height
    .Height = sSize
    .Width = sSize
    .IncrementLeft Range("H1").Width - .Width
    .IncrementTop 2
    .Name = "Picture557"
  End With

  Selection.OnAction = "Macro102"

End Sub

Sub Macro102()
  Dim xfer1 As String

  xfer1 = Application.Caller

  Range("H1").Select
  ActiveSheet.Pictures(xfer1).Delete
  
  Dim sSize As Single
  With Range("H1")
    .Select
    .Parent.Pictures.Insert("C:\up.jpg").Select

  End With

  With Selection.ShapeRange
    sSize = Range("H1").Height
    .Height = sSize
    .Width = sSize
    .IncrementLeft Range("H1").Width - .Width
    .IncrementTop 2
    .Name = "Picture557"
  End With

  Selection.OnAction = "Macro101"

Exit Sub

Macro1 sets up the initial 'button' and sets up the link to macro101.

After that clicking the button toggles between the two pictures and macros.

The pictures I'm using are screen captures of a big 'arrow', 3.5 point as I recall so they look like small solid triangles, pointing up or down.

All of these commands that you guys use...I can't find anything on the internet that lists them. Is there a reference book available please?

I can find the basics by using 'record new macro' and tweak it from there, but I'm missing a lot of info.

Regards
Peter

 

[Loomah]

hi peter
hope your solution is working for you.

as for sources of programming knowledge it might help a little if i tell you that i basically tried recording what you seemed to be after!

after that it was a case of looking to see what properties and mthods were available to me in the case of, say, shaperange. i'd never used this before yesterday but from experience could guess at the types of properties/methods i'd like to be able to use to edit and move the shape.

i also used the help files quite a bit.

in terms of learning the same answers come up on this forum - record, look at, edit and experiment. also keep an eye on the solutions and try to answer other people's problems. i've learnt something from trying to help you. in addition combo has highlighted a few things that i'd not thought of which, most likely, would improve the solution.

a common phrase after all the time i've dipped into these forums is "there's more than one way to skin a cat!!"

as for books, most commonly recommended here are (were?) john walkenbach's books (

http://www.j-walk.com).

even got one myself a couple of years agao - excel 2003 power programmers, or something like that. more of a tutorial than a pure reference. i still use o'reilly's (publisher) "vb & vba in a nutshell" as a pure reference but it's not application specific so unlikely to have been much help here.

also make use of the help files - not always great but always a good starting point. try the object browser too (hit f2 in the vbe).

one thing you will find out, though - it's rarely as straight forward as going to 1 source for a plain english explanation!!

good luck & enjoy!
happy friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Ditto to what Loomah has stated.

Let me add that life, and in this case, skill with VBA, is an on-going, and hopefully, growing experience. Mine could be traced back many decades to learning Dartmoth BASIC on a DIGITAL Computer PDP8, with a teletype machine for I/O. It proceeded in bitty spurts, until 1994 when I found Excel VBA & 2001 for Tek-Tips. Then it began taking off, because I would read interesting posts and then TRY IT. I read and TRIED MORE. In a way, I became obsessed with coding and Excel functionality. I guess I stll am.

Try, try, try. Try new ways to do what you already know how to do. It helps if you actually have some task or project to use your new-found skills on.

My wife just took training in real estate pre-foreclosure short sales just last week. There were 3 categories of mortgage that each had a different set of parameters for calculating what the list price, minimum to the lender etc. IMMEDIATLEY, I began formulating a spreadsheet solution, and, in fact, coded one. She will never use it, because she got a calculator with the course, but that's the kind of mind set that will help you develope your skill.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[peterd51]

Hi,

thanks for the input.

It looks a lot different now. The initial code will be run from VB and that just loops around creating the buttons & adding the macro link. It then writes the macro to Excel, adds the data and it's finished.

The two button macros now do a check on column.count and row.count to calculate the sort range. They calculate the column from the 'Called' value and change the picture, reassign the 'other' macro and run the sort.

That's pretty close to being finished!

From Loomah:
'after that it was a case of looking to see what properties and mthods were available'

this is the bit that has me puzzled. I tried using help but it didn't help me much. I tried a general search and a search on Tek-Tips. I really struggle to find properties, methods, etc, and sometimes resort to reading every bit of code I can find on here looking for clues.

As you say, it's sometimes a case of playing around until I find the right combination and sometimes I leave it 'ugly' but working.

I learnt to program Quick Basic and Z80 in the early 80's and there was always a reference book to look at. This seems at times as if MS want to keep it a secret!

Anyhow, I've added another handy text file to my small collection of Macro codes.

Thanks for your help.

Regards
Peter

 

[Loomah]

this is the bit that has me puzzled

sorry! all it means is looking into the help file to see the list of properties and methods and (often) guessing what to use and trying. shaperange collection is the prime example here. pate into immediate window, highlight, f1 - that's all there is to my 'secret'!!!

coding for me is very much an iterative process. after posting the code for you i went back to look at how it could be done without selecting objects. ended up seeming a little pointless though!

i started with basic at school in the early 80s but after that went nowhere near a computer until the mid 90s! started vba shortly after that but was hopeless! i actually thought that things like xlDown where "multiply by one" Down - now there's a friday confession!! did a 5 day course in 99 but only really started to learn around 2001-02 when i had long periods of unemployment and i'd been itroduced to tek-tips.

unfortunately i don't retain knowledge very well but do develop awareness quickly so i might be aware that something could be done but probably don't know how to do it!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​