(Easy) Code for a macro button

[andrewb90]

Hi all,

I am somewhat new to this, but I have allot of the pieces of code that I need, but I need two other things doe in my macro, and I am not sure how to do them.
1: I need to have a picture in the worksheet hidden(picture identified by its name I would imagine)
2: I need to click on a cell and retype the formula that is in it automatically. (Or if that is not possible, have it type in a number based on the contents of a cell on another sheet.

I hope this is pretty simple stuff.

Thanks in advance,

Andrew

 

[mikerickson]

Part 1, you can record with the Macro Recorder.
Part 2 isn't clear to me, but again, it sounds like the Macro Recorder might suffice.

 

[andrewb90]

What I want to do with part 2 is this:
I have a data validation used in reference to this cell, and Data Validation does not work with cells that populate on their own.
I am trying to either click on the cell as if to type in it, or retype the formula that is in the cell (I can provide the exact formula) so that it seems like I just typed directly into the cell, and therefore my Data Validation will function properly.

 

[andrewb90]

Also, for part 1. I am not sure who I would manually hide a picture.

 

[Michael M]

Hi Andrew
For part 1,try

Sub Macro1()
    ActiveSheet.Shapes("Picture 1").Visible = False ' change picture name to suit
End Sub

I'm afraid I don't follow part 2, but I'm guessing it's a flow on from a previous post based on a similar question. I think I may have suggest some sample data might help

 

[andrewb90]

For part 2 I want to take cell M3 and type in the formula =Welcome!$T$107

I assume when I do that my Data Validation will work even if it is a macro typing something into a cell.

 

[Michael M]

Assumiong your data validation is M3, I think you could simply use "Any Value" from the list selection, to place a formula in the cell

 

[andrewb90]

I don't quite follow what you mean by that.
The Data Validation I have in place is for cells D5:F9 and it gives a popup error if M3 does not equal 1-5. The problem that I have is M3 gets filled out because of its formula in reference to another sheet.

The data Validation only seems to work when I manually type the number into M3 and that will not work for me, since M3 will be hidden and protected from the user.

 

[mikerickson]

Data Validation on D5:F9 would only fire when one of those cells is changed.
In your case, if M3 < 1 or 5 < M3 then it will prevent data entry in D5:F9.

Data validation asks the question "Can this value be entered into this cell?"
It does not ask the question "Is the sheet in good shape?"

Are there a chain of formulas such that changing D5:F9 changes the value in M3? or vice-versa