Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    excel with Visual Basic (VB)

    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").F ormula = "=(.01+(100*c87)*1.06)-(.1*c87*c87*100*100)"
    If Worksheets("Sheet2").Range("c28") < 0.0301 Then ActiveWorkbook.Worksheets("Sheet2").Range("g88").F ormula = "=(.01+(100*c28)*1.06)-(.1*c28)))"
    If Worksheets("Sheet2").Range("c85") < 0.0301 Then ActiveWorkbook.Worksheets("Sheet2").Range("g86").F ormula = "=(.01+(100*c85)*1.06)-(.1*c85*c85*100*100)))"
    End Sub

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: excel with Visual Basic (VB)

    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.

  4. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel with Visual Basic (VB)

    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.

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: excel with Visual Basic (VB)

    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.

  6. #5
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel with Visual Basic (VB)

    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.

  7. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: excel with Visual Basic (VB)

    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

  8. #7
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel with Visual Basic (VB)

    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.

  9. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel with Visual Basic (VB)

    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:

    <code>
    =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))
    </code>

    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.
    Legare Coleman

  10. #9
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel with Visual Basic (VB)

    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.

  11. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel with Visual Basic (VB)

    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.
    Legare Coleman

  12. #11
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel with Visual Basic (VB)

    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

  13. #12
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: excel with Visual Basic (VB)

    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
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  14. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel with Visual Basic (VB)

    I strongly suspect the only problem you have is that calculation is set to manual (No I haven't read the entire thread <img src=/S/smile.gif border=0 alt=smile width=15 height=15>).
    Tools, Options, Calculation tab, check "Autmatic"?

    EDIT: OK, I got to the last messages and indeed calc was manual.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •