Macro Help

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
Joined
Sep 4, 2003
Messages
4,916
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
 

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)
 
Joined
Sep 4, 2003
Messages
4,916
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top