Simple VB question

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,042
Office Version
  1. 365
Platform
  1. Windows
I have the following complex(!) macro;

Sub Post()

Range("B10").Select
ActiveCell.FormulaR1C1 = "on"
Range("B10").Select
ActiveCell.FormulaR1C1 = "off"
Range("B11").Select
End Sub

I have set up a button to run this macro.

When the user presses the button, I want something to appear saying "are you sure?" before the above is run. Options would be yes or no, and "yes" would run the macro, "no" would just return to the spreadsheet without doing anything.

Is there something I can insert into the above to achieve this?

Thanks (from a VB non-user)
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
While I don't really understand the purpose of the macro...but...

try this

Code:
Sub Post() 
If Msgbox("Are You Sure", vbYesNo) = vbYes Then
    Range("B10").FormulaR1C1 = "on" 
    Range("B10").FormulaR1C1 = "off" 
    Range("B11").Select ' Not Necessary to select cells..
End IF
End Sub
 

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,042
Office Version
  1. 365
Platform
  1. Windows
The macro links the spreadsheet into our new accounts system dynamically.

If cell B10 is "off" then fine

But if cell B10 is "on" then it uploads the spreadsheet data into our accounts system

Its a very nice piece of kit but very easy to forget to turn it off, and for someone to repeatedly upload the data without realising!

So I'm trying to prevent this.

Thanks for the above help - works fine!!
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566

ADVERTISEMENT

Hi
It appears to go on and off simultaneously. Does it still upload the data?
ravi
 

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,042
Office Version
  1. 365
Platform
  1. Windows
Yes - as soon as "on" is entered, the data is uploaded.

Pretty crude hence why I wanted to build in some idiot-proofing!

I have adapted it as below;

Sub Post()

If MsgBox("Are you sure you want to post this journal?", vbYesNo) = vbYes Then
If MsgBox("Are you really sure? No going back if you click YES!", vbYesNo) = vbYes Then
Range("B10").FormulaR1C1 = "on"
Range("B10").FormulaR1C1 = "off"
End If
End If
MsgBox ("Journal posted")
End Sub


However I only want the final message to appear if yes was selectyed in both of the first 2 message boxes - at the moment it displays it whatever happens!

Thanks for your help!
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Anything you want to happen "ONLY IF", should be contained INSIDE The IF statements..

Code:
If MsgBox("Are you sure you want to post this journal?", vbYesNo) = vbYes Then 
If MsgBox("Are you really sure? No going back if you click YES!", vbYesNo) = vbYes Then 
Range("B10").FormulaR1C1 = "on" 
Range("B10").FormulaR1C1 = "off" 
MsgBox ("Journal posted") 
End If 
End If 
End Sub

curious though, why do you have it change B10 to ON then OFF?
after user says yes twice,
the code as you have it will always end up as OFF.


Where you wanting it to do ...

IF user says YES, then B10 = ON
IF user says NO, then B10 = OFF

?

That would make more sense...

Code:
If MsgBox("Are you sure you want to post this journal?", vbYesNo) = vbYes Then
    If MsgBox("Are you really sure? No going back if you click YES!", vbYesNo) = vbYes Then
        Range("B10").FormulaR1C1 = "on"
        MsgBox ("Journal posted")
    Else:
        Range("B10").FormulaR1C1 = "off"
    End If
Else:
    Range("B10").FormulaR1C1 = "off"
End If
 

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,042
Office Version
  1. 365
Platform
  1. Windows
I know, its a strange one really.

The manual process for the spreadsheet upload is;

- cell B10 contains "off" - nothing happens
- when the user wants to upload the data, they type "on" into cell B10
- the user must then remember to type "off" again into cell B10, otherwise the data repeatedly uploads into the accounts system.

I myself have inadvertantly uploaded the data 7 times into our training company data, so I want to prevent others doing it!

The basic upload is a great feature, but is far to un-foolproof without these safeguards.

Thanks for your help.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,088
Messages
5,768,034
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top