1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Macro Help

Discussion in 'Business Applications' started by redlion, Jul 12, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. redlion

    redlion Thread Starter

    Joined:
    Jul 6, 2006
    Messages:
    7
    Hi Guys,

    I have the following macos, when i click on NO it still takes be to the sheet called "Data" i want it to stay in the current sheet called "Home" where the macro buttos are.

    Sub Enter_Data()
    MsgAdd = MsgBox("Would You Like To Enter New Data?", vbYesNo, "Please Enter Data")
    Sheets("Data").Select
    Range("A1:U1").Select
    If MsgAdd = vbYes Then ActiveSheet.ShowDataForm
    End Sub

    Sub Clear_Data()
    MsgAdd = MsgBox("You are about to clear all data?", vbYesNo, "Clear Data?")
    Sheets("Data").Select
    Range("A2:U4").Select
    If MsgAdd = vbYes Then Selection.ClearContents
    End Sub
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    You need to combine your IF statement with your messagebox action. Here is an example

    Code:
    If MsgBox("Would You Like To Enter New Data?", vbYesNo, "Please Enter Data") = vbYes Then
    'your code here to execute if YES selected
    Else
    'your code here to execute if NO selected
    End If
    if you want to keep your current code you should test for the numeric values generated when vbYES (value of 6) or vbNo (value of 7) are selected.

    Code:
    Sub Enter_Data()
    MsgAdd = MsgBox("Would You Like To Enter New Data?", vbYesNo, "Please Enter Data")
    Sheets("Data").Select
    Range("A1:U1").Select
    If MsgAdd = 6 Then ActiveSheet.ShowDataForm
    End Sub
    


    Rollin
     
  3. redlion

    redlion Thread Starter

    Joined:
    Jul 6, 2006
    Messages:
    7
    Thanks Rollin works a charm


    One Other Question

    I was just wondering is there is a way to run a macro to automatically calculate the list of formulas in one go. The data is in a sheet called data. Would like it to put the answers in column E9,E10 ect.

    B9 Current Ratio =SUM(B2/C2)
    B10 Total Asset Turnover =SUM(K2/L2)
    B11 Debt-to-Equity =SUM(C2+O2)/P2
    B12 Dividend Per Share =SUM(T2/S2)
    B13 Luqid Ratio =SUM(B2-I2)/(C2-F2)
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    You should be able to just record a macro while manually entering the formulas on the spreadsheet. Then just cut and paste the code into your existing code.

    Rollin
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/482704

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice