Creating a Worksheet_change in VBA

[mark molloy]

Hello,

Apologies if my wording is not entitely correct I am new and still finding my way around.

The issue that I have is that I have an excel spreadsheet with a formula in cells J2:J25 and is populated when a user inputs data into cells A2:25. Rather than update each time data is added I would like the user to be able to click on a macro button once all data is added and then hey presto the results appear.

From looking around the site I beleive this is done via a Worksheet_Change section in the code but as I have just started coding I am struggling as to where it comes in - here is what I have so far.

PrivateSub CommandButton1_click()
Range("J2:J25").Formula = IF(ISBLANK($A2),"",VLOOKUP($G$1&$A2,'account mapping'! $E:$H,4,FALSE))
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
???? Not sure what needs to go here ????

Again apologies if this is way off the mark or very vague I hope to get better!

If anyone can point me in the right direction (video/article/previous post) or offer any advice I would be most grateful

Thanks

Mark.

 

[Luke M]

I'm not sure a change event macro is needed. If you don't want anything to happen until a button is clicked, there's no need to be calling a macro at every change. Rather, how you could set things up:

1. User inputs data into A2:A25
2. When done, macro copies values from A2:A25 to some cells out of the way (say, Z2:Z25)
3. Your formulas reference cells Z2:Z25

This way, formulas aren't getting updated during step 1.

If you want to know more about event macros, I'd suggest starting here:
http://www.cpearson.com/excel/Events.aspx

 

[mark molloy]

Many thanks for that I will take a look and see how that works.

 

[SirJB7]

Hi, mark molloy!

And what if you leave everything as it is now and just change the way how/when Excel calculate formulas?

By default, calculation mode is set to automatic, so every time you enter data in a cell which is referenced elsewhere by a formula, this formula result is calculated and displayed. If you toggle calculation mode to manual (Formulas tab, Calculation group, Calculation Options icon), you can enter data as you described and then instead of clicking on a button just press F9 and Excel will recalculate the workbook.

Would it do the job?

Regards!

 

[mark molloy]

Thanks for the reply - only thing is this spreadsheet will not just be used by myself, it will be used throuought my company so to get everybody to do this would be quite hard.

 

[NARAYANK991]

Hi Mark ,

I am a little confused by what exactly you want.

The code you have given , which I am reproducing below , is introducing the formulae in J2 through J25.

Private Sub CommandButton1_click()
            Range("J2:J25").Formula = IF(ISBLANK($A2),"",VLOOKUP($G$1&$A2,'account mapping'! $E:$H,4,FALSE))
End Sub

I presume that you already have a button on your worksheet , which you can click so that it runs the above macro.

Is your issue only that you want all results to appear at one go rather than as each cell in column A is populated ?

If so , what you can do is just add one statement to the above macro , changing it to :

Private Sub CommandButton1_click()
            Range("J2:J25").Formula = "=IF(ISBLANK($A2),"""",VLOOKUP($G$1&$A2,'account mapping'! $E:$H,4,FALSE))"
            [J2:J25].Value = [J2:J25].Value
End Sub

What this does is convert all the formulae to values ; thus when you now change the values in column A , nothing changes in column J till you once more click the button.

Narayan

 

[mark molloy]

Hi Narayan,

I will give you the back ground to this.

A work colleauge set up this spreadsheet with some macros and has since left the company and did not leave the passwords - I am trying to replicate his spreadsheet from looking at the formulas he has and how the spreadsheet works (you fill in fields in column A, hit the macro button and it then populates the output in colum J). I had a dig around on the internet and from my (very limited) vba knowledge I assumed that the macro contained a worksheet_change.

I currently don't have the macro set up just the spreadsheet with the formulas so I was looking at how I could replicate the action that he uses.

The file is .xls so I know that using a Hex Edit I could probably find the password for the VBA but I am just concerned about downloading something that may affect my work laptop. If there is one that is completely safe then I would be willing to try that.
As I know the password to unprotect the worksheets I tried copy and pasting each worksheet to a new file but I still am unable to view the macro - when I hit the macro button it just opens the old file.

Apologies for the lojng post but I thought it better to give as much info as possible in the hope it might help. I'm sure posting this on other sites would have resulted in far less response so I am extremely grateful for the help so far.

Thanks,

Mark.

 

[SirJB7]

Thanks for the reply - only thing is this spreadsheet will not just be used by myself, it will be used throuought my company so to get everybody to do this would be quite hard.

Hi, mark molloy!

I don't think so, it wouldn't be so hard. You can place this:
Application.Calculation = xlCalculationManual
in the workbook open event code and this in the deactivate event:
Application.Calculation= xlCalculationAutomatic

You can either display a message box telling users to press F9 or place a command button that recalculates the workbook.

Regards!

 

[NARAYANK991]

Hi Mark ,

Thanks for the details. If you try out the code I posted earlier , which I am reproducing below , it will do the following :

1. When you click the button , which I assume will be clicked only after all data in the range A2 through A25 has been entered , the code will insert formulae in the range J2 through J25.

2. Thereafter the code will copy paste the formulae in the same range , so that all the formulae are replaced by their values.

This ensures that when you next enter data in the range A2 through A25 , the values in the range J2 through J25 are updated only when you click the button.

The code itself is just 2 lines :

Private Sub CommandButton1_click()
            Range("J2:J25").Formula = "=IF(ISBLANK($A2),"""",VLOOKUP($G$1&$A2,'account mapping'! $E:$H,4,FALSE))"
            [J2:J25].Value = [J2:J25].Value
End Sub

Narayan