Simple VB question

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,055
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
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!!
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top