VBA - Check and run macro only once

[j4ymf]

Morning

I have a macro, can some one please help me change it and write the code to check if the macro have run before when the button is pressed
if it has please return a message saying sorry this has be run before. then end the macro

my thoughts were to have a look and see if anything was in cell """" Destination:=Sheets("P1").Range("A13")) ""
if not run the macro

many thanks jason

Sub GetWebPage_P1()
    'Copy web page
    On Error GoTo error
    Application.ScreenUpdating = False
    
        With Sheets("P1").QueryTables.Add(Connection:= _
            "URL;" & Sheets("Data").Range("B22").Value, _
            Destination:=Sheets("P1").Range("A13"))
        
            .BackgroundQuery = True
            .TablesOnlyFromHTML = True
            .Refresh BackgroundQuery:=False
            .SaveData = True
        End With
        
         Sheets("Test").Activate
    Range("L12:Y764").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("P1").Select
    Range("L13").Select
    ActiveSheet.Paste
    Columns("L:L").ColumnWidth = 16.83
    Columns("V:V").ColumnWidth = 14.5
    Range("A1").Select
        
     
error:
Application.ScreenUpdating = True
End Sub

 

[mole999]

i just get the macro to place a charter on a sheet when run, and then when its called again check to see if the mark exists, if it does exit sub

 

[j4ymf]

mmm

thanks mole99
I've been thinking the same

can you write an example for me please or re write my code to suit?

I'm thinking something like this!

If ISBLANK(Sheets("P1").Range("A13"))= True
     MsgBox "Sorry, This button has already been pressed!"
Else
        With Sheets("P1").QueryTables.Add(Connection:= _
            "URL;" & Sheets("Data").Range("B22").Value, _
            Destination:=Sheets("P1").Range("A13"))
[\code]

 

[mole999]

at the beginning of the code

If Sheets("P1").Range("A1")= "X" Then Exit Sub

at the end

Sheets("P1").Range("A1") = "X"

 

[j4ymf]

thank you mole ill give it a try

 

[Steph77]

You should also probably put something in a workbook_beforeclose sub to remove the X if you want them to be able to save it and then run it next time it's opened.

 

[j4ymf]

mole

why can i get a message box to work

If Sheets("P2").Range("A1") = "x" ._
MsgBox "Sorry, This button has already been pressed!"Then Exit Sub

thanks for your help

 

[Steph77]

Could it be that you haven't capitalised X?

 

[MARK858]

Or the "Then" being in the wrong place

Either...

Sub xxx()
    If Sheets("P2").Range("A1") = "X" Then MsgBox "Sorry, This button has already been pressed!"
    Exit Sub
End Sub

or

Sub XXX2()
    If Sheets("P2").Range("A1") = "X" Then
        MsgBox "Sorry, This button has already been pressed!"
        'OTHER ACTION
        'OTHER ACTION
        'OTHER ACTION
        Exit Sub
    End If
End Sub

 

[j4ymf]

sorry im still on this one
confusing me a little

how do i get in an else or somthing after the exit sub? if the button hassnt been pressed
or it does nothing???
thanks jason

Sub GetWebPage_P2()
    'Copy web page
    On Error GoTo error
    Application.ScreenUpdating = False
    
    If Sheets("P2").Range("A1") = "X" Then MsgBox "Sorry, This button has already been pressed!"
    Exit Sub
    
    
       With Sheets("P2").QueryTables.Add(Connection:= _
            "URL;" & Sheets("Data").Range("B23").Value, _
            Destination:=Sheets("P2").Range("A13"))
        
            .BackgroundQuery = True
            .TablesOnlyFromHTML = True
            .Refresh BackgroundQuery:=False
            .SaveData = True
        End With
        
        
        Sheets("Test").Activate
    Range("L12:Y920").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("P2").Select
    Range("L13").Select
    ActiveSheet.Paste
    Columns("L:L").ColumnWidth = 16.83
    Columns("V:V").ColumnWidth = 14.5
    Range("A1").Select
    
    Sheets("P2").Range("A1") = "X"
     
error:
Application.ScreenUpdating = True
End Sub