excel with Visual Basic (VB)

Topic

New Reply

Hans, Is there a better way of coding this? what happens that when I click on the CommandButton1 to update the sheet after the user updates his specs. one column only gets updated, he clicks on update again the next column updates and so on. I need it so that he clicks only once and everything gets updated. What am I doing wrong. Please advise. Code is below.

Private Sub CommandButton1_Click()
Worksheets(“Sheet2”).Range(“b8”).Formula = “.489ID x.070C/S”
Worksheets(“Sheet2”).Range(“d19”).Formula = “=C22/((((B16-B17)*(B16-B17))-((B14+B15)*(B14+B15)))*3.1415159/4)”
Worksheets(“Sheet2”).Range(“C22”).Formula = “=2.4674*(B10+B12+B13+B11)*((B12+B13)*(B12+B13))”
Worksheets(“Sheet2”).Range(“C23”).Formula = “=(((B16-B17)*(B16-B17))-((B14+B15)*(B14+B15)))*C18*3.14159/4”
Worksheets(“Sheet2”).Range(“d19”).Formula = “=C22/((((B16-B17)*(B16-B17))-((B14+B15)*(B14+B15)))*3.1415159/4)”
Worksheets(“Sheet2”).Range(“b27”).Formula = “=(e27-(i27-g27)/2)”
Worksheets(“Sheet2”).Range(“b28”).Formula = “=(e28-(i29-g28)/2)”
Worksheets(“Sheet2”).Range(“b29”).Formula = “=(e29-(i28-g29)/2)”
Worksheets(“Sheet2”).Range(“C27”).Formula = “=(b14-b10)/b10”
Worksheets(“Sheet2”).Range(“C28”).Formula = “=((B14+B15)-(B10-B11))/(B10-B11)”
Worksheets(“Sheet2”).Range(“C29”).Formula = “=((B14-B15)-(B10+B11))/(B10+B11)”
Worksheets(“Sheet2”).Range(“e27”).Formula = “=(b12*(1-.01*g86))”
Worksheets(“Sheet2”).Range(“e28”).Formula = “=(b12+b13)*(1-(.01*g87))”
Worksheets(“Sheet2”).Range(“e29”).Formula = “=(b12-b13)*(1-(.01*g88))”
Worksheets(“Sheet2”).Range(“g27”).Formula = “=(B14)”
Worksheets(“Sheet2”).Range(“g28”).Formula = “=(B14+B15)”
Worksheets(“Sheet2”).Range(“g29”).Formula = “=(b14-b15)”
Worksheets(“Sheet2”).Range(“i27”).Formula = “=(b16)”
Worksheets(“Sheet2”).Range(“i28”).Formula = “=(b16+b17)”
Worksheets(“Sheet2”).Range(“i29”).Formula = “=(b16-b17)”
Worksheets(“Sheet2”).Range(“b32”).Formula = “=B14+B15+(2*(B12+B13))”
Worksheets(“Sheet2”).Range(“c85”).Formula = “=(b14-b10)/b10”
Worksheets(“Sheet2”).Range(“c86”).Formula = “=((B14+B15)-(B10-B11))/(B10-B11)”
Worksheets(“Sheet2”).Range(“c87”).Formula = “=((B14-B15)-(B10+B11))/(B10+B11)”
Worksheets(“Sheet2”).Range(“c88”).Formula = “=(e27-(I27-G27)/2)”
Worksheets(“Sheet2”).Range(“e85”).Formula = “=(e27)”
Worksheets(“Sheet2”).Range(“e86”).Formula = “=(e28)”
Worksheets(“Sheet2”).Range(“e87”).Formula = “=(e29)”
Worksheets(“Sheet2”).Range(“g85”).Formula = “=(b14)”
Worksheets(“Sheet2”).Range(“g86”).Formula = “=(.56+(.59*c85*100)-(.0046*100*100*c85*c85))”
Worksheets(“Sheet2”).Range(“g87”).Formula = “=(.56+(.59*c87*100)-(.0046*100*100*C87*C87))”
Worksheets(“Sheet2”).Range(“g88”).Formula = “=(.056+(.59*100*c28)-(.0046*100*100*C28*C28))”
Worksheets(“Sheet2”).Range(“i85”).Formula = “=(b16)”
Worksheets(“Sheet2”).Range(“i86”).Formula = “=b12*(1-g86)”
If Worksheets(“Sheet2”).Range(“c87”) < 0.0301 Then ActiveWorkbook.Worksheets("Sheet2").Range("g87").Formula = "=(.01+(100*c87)*1.06)-(.1*c87*c87*100*100)"
If Worksheets("Sheet2").Range("c28") < 0.0301 Then ActiveWorkbook.Worksheets("Sheet2").Range("g88").Formula = "=(.01+(100*c28)*1.06)-(.1*c28)))"
If Worksheets("Sheet2").Range("c85") < 0.0301 Then ActiveWorkbook.Worksheets("Sheet2").Range("g86").Formula = "=(.01+(100*c85)*1.06)-(.1*c85*c85*100*100)))"
End Sub

Reply | Quote

Replies

In the first place, please don’t assume that I will always be the one replying.

Why do you need code for this? All formulas except the last three are fixed, and even the last three could be made conditional by using the IF function.

You could put a line

Worksheets(“Sheet2”).Calculate

immediately above End Sub to force the worksheet to recalculate.

Reply | Quote

You always are the person who responds to my situations thats why I point you out. if you prefer me not to do so next time I will not. yes the formulas are fixed but right now if you would click on the cell you would see said formula. I would prefer the user not see the formula what so ever. Also would that Worksheets(“Sheet2”).Calculate calculate everything only once or would I still be clicking on the CommandButton1 to update it. Could I just eliminate the button all together. If so Please advise. I have been working on this for almost a full 7 days now and skimming throught the Excel book as well. But I still feel kind of lost on this one.

Reply | Quote

Could you please explain clearly what you want to accomplish? The code you posted will put formulas in cells, yet you say that you don’t want formulas. And what do you mean by “Could I just eliminate the button all together”? Don’t you want to run this code? I’m utterly confused.

Reply | Quote

Yes I want to run the code. Just would prefer the user not see the code when they are on a certain cell. It might confuse them. Is there a way to write this code without using the commandbutton1 to update everything. I would prefer that when the client updates a certain cell say cell b10 everything else automatically calculates by itself instead of the user clicking the CommandButton1.

Reply | Quote

You can create a Worksheet_Change event procedure:
– Right-click the sheet tab.
– Select View Code from the popup menu.
– Enter code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
‘ Check if user changed B10
If Not Intersect(Target, Range(“B10”)) Is Nothing Then
‘ Temporarily disable other events
Application.EnableEvents = False
‘ Code to change other cells goes here
…
‘ Enable other events again
Application.EnableEvents = True
End If
End Sub

Reply | Quote

Iapologize Hans for my own stupidity but I really do not understand what your trying to do there. I’m really just starting out with this programming stuff and I do not see intersect in the book… The cells that the user updates are B10 through B17. everything else should automaticaly calculate once they hit the enter button. But it does not that is why I have the CommandButton1 there. Is there a way to set it up so that when they hit enter after keying in one of the cells everything is updated.

Reply | Quote

I strongly suspect the only problem you have is that calculation is set to manual (No I haven’t read the entire thread ).
Tools, Options, Calculation tab, check “Autmatic”?

EDIT: OK, I got to the last messages and indeed calc was manual.

Reply | Quote

I’m not 100% sure I understand what you are trying to accomplish. However, if the reason you are using a macro attached to a button to insert the formulas is to keep the user from seeing calculation errors in those cells before they insert their data (for example the #DIV0 error in cell D19), then you could change your formulas to eliminate the errors. For example, the formula in cell D19 could be changed to:


=IF(ISERROR(C22/((((B16-B17)*(B16-B17))-((B14+B15)*(B14+B15)))*3.1415159/4)),"",C22/((((B16-B17)*(B16-B17))-((B14+B15)*(B14+B15)))*3.1415159/4))


This formula will display an nothing if your original formula results in an error value, but will display the result if it does not. You can place that formula in the cell and wait for the user to enter the data. No need for the macro at all. If you are trying to accomplish something else, please give us a better description of what you want.

Reply | Quote

Hi, no the problem is when the user enters any data in cells B10 through B17 then hits enter the whole page should be calculated (this should happen after each cell has been updated I.e…. Cell B10 change from .222 to .333 enter – then sheet updated) but it’s not doing that I had to creat a command button to update the sheet everytime the user enters a new spec in a different cell like B10 – (update cell b10, hits enter- nothing happens, clicks command button1 updated, etc..)

All I want to know is how do I get my code to update after any cell from B10 to B17 is updated and the user hits enter.

Reply | Quote

Click on the Options command on the Tools Menu. In the dialog box click on the Calculation tab. Make sure that the Calculation Automatic radio button is selected. If this button is selected, then the worksheet should recalculate whenever any on the dependant cells is changed. If this does not help, could you upload the workbook? Change any sensitive data if necessary.

Reply | Quote

That did it, whew! that nailed it on the head. I knew there was something wrong somewhere I just could not figure it out. I don’t really know all the Jargon that goes with VB and Excel. But the more I work with it the more familiar I become with it. So I hope that you all will excuse my Ignorance and or stupidity at trying to explain what I need. I thank you all so Very much for any and all assistance that you give. Out of all the sites I’m registered with, I always find myself coming back to this one to get the problem resolved. Thanks

Reply | Quote

This code snippet, strategically placed before and after the core VBA code, may be useful;

Dim intCalcSet As Integer
‘ Save the users setting for xlCalculation:
intCalcSet = Application.Calculation
‘ Turn off calculations… speeds up VBA code:
Application.Calculation = xlCalculationManual
‘ Insert core code here
‘ When done, recalc and reset back to user preference:
Application.Calculate’ (or Application.CalculateFull in later versions of XL)
‘ Restore original user setting:
Application.Calculation = intCalcSet

Reply | Quote