PDA

View Full Version : Macro works part does not change the picture



k0st4din
09-12-2012, 05:20 AM
Hi all,
I have a problem with some amazing pictures of kitchen cabinets.
Because I'm not very adept at writing macros, made them lazy way by clicking on the sheets, but I think it's okay that was not the case. Here's the problem: excel file with many sheets, but just talking about 2 -> first named -> "Formuli", the second named -> "Formulas for the calculation." In the first box I L2 Dropdown 1 to 17 - each number is the number of image corresponding to the cabinet of the table to the left of the yellow square (1 is the first cabinet, 2 is the second etc. to 17).
Now - because I do not know how I did it like this ->
1. Right-click on the first picture (1 rack) -> Assingn macro
2. Window in it - Record macro - next is -> OK
3. It clicks and write what I want to happen.
4. Clicks L17, then "Formulas for calculating" - select cells D2: I28
5. I'm back in "Formuli" cell L17 - paste special value (required)
6. I'm going to drop down menu and choose L2 "Fig.1" (this is the first cabinet) - picture is shown above in the yellow square (you'll see).
7. Pressing Stop macro regording
Everything should be done, but alas only half. Suppose that I have chosen from the menu a different cabinet than 1, go and push the image of the first cabinet in the blue-green field - then the table (in the yellow box is transferred, but the picture does not change - which is actually my biggest problem ).
And I do not know why it does not want to change it????
link to fail ->dox.bg/files/dw?a=cc235786a2

Teeroy
09-14-2012, 10:33 PM
Hi k0st4din,

Please attach a sample excel workbook directly to your post. Trying to get the sample from the share site is impossible for anyone not familiar with the Cyrillic alphabet.

Recording macros is a good way to start VBA but it produces very inefficient code. You usually edit your code in the VBE to get the final product.

k0st4din
09-15-2012, 03:52 AM
Sub fig_1()
'
' fig_1 Macro
'Somewhere here I can not see the action that perform the selection in Fig. 1
'(just an example, it should be for everyone else to 17),
'just not in the macro code, but actually doing it.
'
Sheets("Formuls calculating").Select
Range("D2:I28").Select
Selection.Copy
Sheets("Formuli").Select
Range("L17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L12").Select
End Sub

Teeroy
09-15-2012, 04:05 PM
As your data validated list in "Formuli" is changing the figure correctly (I can't exactly see how you've implemented it though) the easiest way to do what you want is to change to value of cell "L2" within the VBA code. The following is for Fig 1 and can be extrapolated for the others. It also cleans up your recorded code.

Sub fig_1()

Sheets("Formuls calculating").Range("D2:I28").Copy
Sheets("Formuli").Activate
Range("L17").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Formuli").Range("L2").Value = Sheets("Snimkite").Range("a2").Value 'change 2nd half for each figure
End Sub