Macros

[RichardU]

How do you add a macro to a cell that works wen the user tabs out the cell?

 

[HalfAce]

Hello RichardU,
I can't believe so many views and no answers to this.
Assuming you have your macro written, and (for example) is named 'Macro1', right
click the sheet tab for the sheet of interest, choose View code and paste this in the
white area that is the sheet code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$A$1" Then Macro1
End Sub

Now you just have to change $A$1 to the real cell you want the code to execute by
clicking out of.

Hope it helps.

(BTW, you don't live in Missouri do you? - I have a brother named Richard U. in Missouri.)

 

[erik.van.geit]

Hi,

What do you mean by "tab out"?
Would this mean that changes were made to that cell?

Then you could use

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Macro1 
End Sub

kind regards,
Erik

Hi, Dan

 

[HalfAce]

What do you mean by "tab out"?

Yeah, I guess that is a good question. . .

Hi Erik! Long time, no see.
(Or at least "Long time, no be awake at the same time" I guess)

 

[erik.van.geit]

Hi Erik! Long time, no see.
(Or at least "Long time, no be awake at the same time" I guess)

long time not in same thread?
I was not very active here last month

 

[RichardU]

If a user uses the "Tab" button on a keyboard into a cell, if they "Tab" out of it and it is blank then it brings up a message and wont let the user continue until they enter data into that cell.

Like a manditory date field for example.

 

[erik.van.geit]

1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub

    If Target = vbNullString Then
    MsgBox "please fill in", 48, "title"
    Target.Select
    End If

End Sub

2.
data validation
see the option "length text"

But these options are not waterproof!
if a user presses "delete" it will not work

I suppose you want to do this for several cells...
Can you tell a bit more about your intentions? Do the entries need to be done in a certain order? Would it be enough to check some cells before printing or closing or ... ?

 

[RichardU]

Well i have a spreadsheet that needs the date and vendor number entered before it can save it or print etc. I have a macro that checks the document over but i wanted to force the user to enter these details when they open the tab, without an input box!

 

[erik.van.geit]

If my understanding is correct:
1. You're system is already waterproof, because you are checking those cells before print, save ... If not the user gets a warning.
2. What you want is some additional feature to put the attention of the user to both cells which need to be filled in.

If the user may not get an inputbox, how do you see this happening?
What do you think of the next suggestions?
1. when sheet is activated and the date is not filled in, it will be selected
2. till it is filled in, it is red
3. till it is filled in, al other cells are locked
4. using another cell with formula like = IF(A1="","Filll in data","")
5. this cell can also have conditional format

same story for vendor
instead of another cell, you could use a label which would get invisible when data and vendor are filled in

So: what is your scenario?

 

[RichardU]

I was hoping that i could have the cells selected when the sheet opens,

If the user enters data and clicks/tabs out of the cell it moves to the other one and does the same

If the cell is blank then have a message box pop up saying they have to fill in the data, on a timer, closing after 3 seconds, and then selecting the same cell again.

Hope this makes sense