Start macro upon cell's change in value, 5 questions

[Guest]

Am novice in VB, I've programmed in fortran, SAS, cobol etc...

Q1) recommendations on Visual Basic books ("geared" to Excel?)

Background for Q's below:
for some, but not all worksheets in a workbook:
data in columns b and d are related.
I want to 'format' d2 based on the value of b2.
user will enter b2 first. about seven values will be valid.
e.g. b2=3, (text format), original value of d2: blank, user enters:
1234 (as text)
I want d2 to look like: [1234]
am currently using =concatenate("[",d2,"]")

Q2) How do I get code to excecute only on worksheets that I want?
I will 'know' some of the worksheets names, but not all.
I suppose I can direct the user to put a specific value into 1
specific cell as a something to check for this decision. Other ideas?

Q3) it appears from other notes that
private sub worksheet_change()
my code
end sub
is the lowest level of event change and will be where I put my code.
True?

Q4) how do I show an simple error msg when b2 contains a bad value and
prevent further data entry? a blank or null cell is a valid value.
(I am familiar with if ... then .... else syntax)

Q5) Don't know if this will be beyond my very near term skill level,
but, in a perfect world, for some of the values in b2, I'd like to place
what the user enters in d2 inside a 'graphic'.
e.g. if b2 = "4" then put 1234 'inside' an oval.
the oval and the 1234 inside it would have to fit within the
boundaries
of the d2 cell.

Whew! and thanks so much. Neal

Neal Z

 

[Bob Phillips]

Am novice in VB, I've programmed in fortran, SAS, cobol etc...

Q1) recommendations on Visual Basic books ("geared" to Excel?)

Check out

http://j-walk.com/ss/books/index.htm

and

http://www.contextures.com/xlbooks.html

Q2) How do I get code to excecute only on worksheets that I want?

Worksheets have specific events that you can tap into. You get to a
worksheet code module by selecting the worksheet name tab, right-click and
select6 View Code from the menu.

Q3) it appears from other notes that
private sub worksheet_change()
my code
end sub
is the lowest level of event change and will be where I put my code.
True?

Don't know about lowest level, but it is a worksheet change that can be used
to trap a cell change.

Q4) how do I show an simple error msg when b2 contains a bad value and
prevent further data entry? a blank or null cell is a valid value.
(I am familiar with if ... then .... else syntax)

Prevent further entry where? In that cell, if so, how do they correct it?

You can show an message like so

If Range("A1") > 10 Then
MsgBox "Cell A1 cannot be greater than 10"
End If

Q5) Don't know if this will be beyond my very near term skill level,
but, in a perfect world, for some of the values in b2, I'd like to place
what the user enters in d2 inside a 'graphic'.
e.g. if b2 = "4" then put 1234 'inside' an oval.
the oval and the 1234 inside it would have to fit within the
boundaries
of the d2 cell.

Graphics are not related to cells in that way, so getting a graphic to be
within a cell is complex, more complex than it is worth IMO.

I would suggest you get a book,. get started, and come back when you have a
specific problem.And it will help if you describe the requirements and
design as best you can.

 

[Guest]

Thanks for the prompt response. Neal.

Check out

http://j-walk.com/ss/books/index.htm

and

http://www.contextures.com/xlbooks.html


Worksheets have specific events that you can tap into. You get to a
worksheet code module by selecting the worksheet name tab, right-click and
select6 View Code from the menu.


Don't know about lowest level, but it is a worksheet change that can be used
to trap a cell change.


Prevent further entry where? In that cell, if so, how do they correct it?

You can show an message like so

If Range("A1") > 10 Then
MsgBox "Cell A1 cannot be greater than 10"
End If


Graphics are not related to cells in that way, so getting a graphic to be
within a cell is complex, more complex than it is worth IMO.

I would suggest you get a book,. get started, and come back when you have a
specific problem.And it will help if you describe the requirements and
design as best you can.