Sleeper: Macro from Sub to Function question

[xCav8r]

It's because you're calling NewHIres without the parameter. It requires a range.

Private Sub Worksheet_Change(ByVal Target As Range)
NewHires "A1:C20"
End Sub

[malik641]

So what if I want the range to be the same as what the function calls for (i.e. rng As Range)??

A new column is added each month and I wouldn't want anyone to have to change the code everymonth...And I don't want to make the range to the end if it is unefficient.

[malik641]

Also getting a Type mismatch with a range type of "C2:C44"....????

[xCav8r]

Whoops, my bad. You need to call it something like this:

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim myRange As Range
Set myRange = Worksheets("Finding text in Comments").Range("C2:C44")
NewHires myRange
End Sub

[Bob Phillips]

Whoops, my bad. You need to call it something like this:

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim myRange As Range
Set myRange = Worksheets("Finding text in Comments").Range("C2:C44")
NewHires myRange
End Sub

or more probably

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
    Set myRange = Worksheets("Finding text in Comments").Range( _
        Cells(1, Target.Column), Cells(1, Target.Column).End(xlDown))
    NewHires myRange
End Sub

[malik641]

Those both work pretty good...xCav8r's is a little too specific for the range. And xld's works only when a change has been made to a cell value in the column that has the changed comment. I think I'm approaching this the wrong way.

I want to make this so that when a comment is changed, and ONLY a comment is changed, to recalculate the formula in that column immediately. I thought I could just get away with the event procedures that you guys gave me (which I REALLY appreciate, BTW). But if the user solely changes an amount of new hires in a particular column (each column is a month, btw) and never changes anything in that column (cell values), then it will never show up. And I know I could just use a different event procedure such as Workbook_Activate or something, but I kind of wanted to make it instant.

So I'll try to figure out something if I can...keep me posted if you guys think of anything. Otherwise thanks for ALL the help, well appreciated !!
And if I think of anything, I'll post it. It may take a bit, though, seeing that I'm such a novice at VBA (and some formulas as well).

[Zack Barresse]

..I want to make this so that when a comment is changed, and ONLY a comment is changed, to recalculate the formula in that column immediately..

No can do hoss. Excel does not recognize this as an 'event'. Kinda stinks, I know. Same with cell formatting (and THAT'S been a pain in my bum..). If you want instant-type results, you'll need to have this information in a column/cell.

[malik641]

Grrr......I'll think of SOMETHING. I mean, what's an engineer for? lol

No can do hoss. Excel does not recognize this as an 'event'. Kinda stinks, I know. Same with cell formatting (and THAT'S been a pain in my bum..). If you want instant-type results, you'll need to have this information in a column/cell.

[Zack Barresse]

LOL!

You want my honest opinion? If you're trying to calculate something, a comment is not the best place for it. I think I would take the time to restructure it as so you can enter this data in an adjacent column.

[malik641]

Trust me, I've thought that over plenty of times....but my range of columns that I have to change is from C to CQ....yeah, no thanks. And I guess I COULD change it only from this month on...but I kinda want to keep things consistent, so that's why I'm stuck doing this junk the way I am. The way I have it setup right now is to select the cells to evaluate, then run a macro (I made it as easy as I could by making a custom button to press). But I wanted to make it THAT much more efficient with this new function feature....
Maybe this is what they'll have to deal with...We'll see.

LOL!

You want my honest opinion? If you're trying to calculate something, a comment is not the best place for it. I think I would take the time to restructure it as so you can enter this data in an adjacent column.

[geekgirlau]

Private Sub Worksheet_Change(ByVal Target As Range)
    NewHires Target
End Sub

[geekgirlau]

Oops - hit that quick reply button without noticing page 2!!

... okay, I get it ... more coffee ...

[geekgirlau]

Have you thought of showing the data in a summary sheet instead? You could display a total # new hires for each month rather than using the comments.

[malik641]

Well the reason for the comments is because there is not just new hires. There are terminations (Involuntary, and Voluntary) and there are transfers and it's just a dumb layout in my opinion. I would have set this up WAY differently. But this was made like 2 years ago...I just started working at my job for like 3 months, so I have to work with what I have. And I really don't want to do an overhaul.

I spoke with the main user and he said it would be fine to make the sheet calculate all the values in each column upon a workbook_open event. So I'll be toying with that today. He also wants me to make a dialog box to pop-up when you press "Insert Comment" either from the right-click or the Insert->Insert Comment menu. In the dialog box he wants me to set it up sort of like the way conditional format's dialog box is set-up....I've never done a dialog box before...but I don't have a deadline, so I can take my time. I put the details in another thread "Userform pop-up when insterting a comment" if you want to check it out.

So I'm going to have fun with that today!

[Zack Barresse]

If you created your own menu/toolbar/button/whatever, you could create your own userform and then make any events you want to off that. Otherwise, there is no event attached to inserting/deleting/adjusting comments. If you're still trying to go that way, it's a dead end.

I'd suggest the custom menu and userform method. That would give you full control over everything the user picks from there. And I'd still suggest having the new hires data go into actual cells..