Posts by sachin123sood

    Hi Dave ,

    I am already using

    Private Sub Workbook_Open()
    ActiveSheet.Protect UserInterfaceOnly:=True
    end sub

    but this is not helping the moment I try to plot the char it is giving the error :

    Method "Location" of object "_Chart" failed
    at statement :

    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

    Sheet1 is the active sheet.

    I am using web queries to fetch the data as :

    With Worksheets("sheet4").QueryTables.Add(Connection:= _
    address_string _
    , Destination:=Worksheets("sheet4").Range("A1"))
    '.Name = "quotesearch.jsp?companyname=gail&series=EQ&flag=0"
    On Error GoTo errorhandler
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .Refresh BackgroundQuery:=False
    End With

    where address_string is my website address.

    some times when internet ( as I am using proxy ) is down then it is displaying the error message that could not open the specified page . I want to suppress this message ( I don't want the message box to pop up with this error and instead want to continue with the next set of statements ) Where I have to put the error handling routine in the code .

    Also when this thing is fetching the data the excel is hanged, Can I avoid this hanging of Excel application by making some changes in the parameters.

    Hi Dave,

    This is not working

    Upon clicking the stop button it is giving error that

    Method ontime of object Application failed.

    Actually my Stop_click proc is in the Sheet1 and my delay macro is in standard module .

    so, what I did is

    sub stop_click ()
    run "stop_myproc"
    end sub

    the above code is in sheet 1 ( the sheet containing the button )

    and in the standard module I inserted the following sub

    sub stop_myproc
    application.ontime dtime, "delay", , false
    end sub

    but this is not working giving the above error.

    also I am a little bit confused about the placing of the
    public dtime as date.

    do I need to put it outside the sub or in side the sub myproc.

    Hi Dave,

    Its a simple statement :

    sub myproc()
    application.ontime now + timevalue ("00:00:30"), "Delay"
    end sub

    where delay is the proc :

    sub delay()
    end sub

    now If I press the button Stop I need to kill this scheduled processing of my proc.
    based on your article I tried

    sub stop_click()
    application.ontime now,"stop_myproc", , false
    end sub

    sub stop_myproc()
    msgbox "stopping myproc"
    end sub

    but this is failing.
    please tell me what I need to include in my stop click routine .

    Ya Dave,

    I have read the page . But the things are not cleared as I want to implement this logic in a button click routine.

    i.e. upon the click of the button all scheduled procs should be killed . and If I have to resume the process again I will click on a separate button which will start the process again .

    Please tell me what I have to code in my stopbutton_click routine.

    Hi I have used sPage as string ...

    Please tell me how to declare this as a Object

    right now I am using Dim sPage as string

    and then using allocating a value to this variable .

    what I am doing is fetching the whole page as a string and then searching for some pattern or strings in this full page ( sPage)

    Is there any alternate way for doing this ......

    I am fetching data from net using SHDocVw.InternetExplorer

    and repeatedly calling the proc to refresh the data
    but while fetching the data in the string using

    Set oIE = New SHDocVw.InternetExplorer
     Dim oIE As SHDocVw.InternetExplorer
    sPage = oIE.Document.body.InnerHTML

    I am getting the error at line "sPage = oIE.Document.body.InnerHTML"

    This is the error I am getting :

    run time error '91'
    object variable or with block variable not set.

    how can i avoid the error or how to handle this error so that I can resume Processing and fetch the next set of data from the web .

    Hi Roy thanks for this sol.

    Can you help in this prob. also

    This is regarding stopping and resuming of a proc

    I want to do the following :

    I have 4 buttons in my worksheet

    button 1 (Operation 1) :

    by clicking on this I am doing certain set of operations (operation1) repeatedly.
    let say its a public sub procedure which I will be looping
    This a procedure which is looped through application.ontime.

    button 2 (Suspend) :

    for suspending the operation1
    current I am using stop command for this functionality. I do not want to use wait as I want to do some other set of operations and do not want to use end as I want to resume from the point where I have stopped.

    button 3 (operation 2)

    And after this i will be able to click a 3rd button (Operation2) to do some other set of operations ( operation 2 )
    operation 2 is a small set of statements executed only once and no looping.

    button 4 (Resume Operation 1)

    after my operation 2 finishes I should be able to click on this button and resume my operation 1 from the point where I have stopped it

    The additional thing here I require is I will pause a macro or a proc at a certain line ( whenever stop button is pressed ) and then will execute some other proc ( get a notification when it will finish ) and after that will resume the operation1 from the same point where it was stopped .

    The code will be similarly be something like this

    'this is button1 to start operation 1

    sub commandbutton1_click()


    end sub

    'this code is for operation 1

    sub operation1()

    'some statements to execute

    application.ontime now + timevalue("00:00:30"), "Call_again"

    end sub

    'this is the repeating procedure

    sub call_again()


    end sub

    ' The code for the Stop button which will execute the stop command and will pause the program execution of operation 1

    sub commandbutton2_click()
    end sub

    now at this point I want to call operation2 through my third button

    sub CommanButton3_click ()
    end sub

    sub operation2()
    some set of statements
    msgbox "operation2 finishes"
    end sub

    after this I want Resume the Operation 1 from the point I have stopped by pressing the button 4 resume .

    sub commandbutton4_click()

    logic for resuming the operation1

    end sub

    please provide some solution .


    Hi Roy

    I need to check the value while inserting in the column

    Most of the discussions in the forums are linked to getting the unique values in the previously filled columns

    I want to implement the following logic

    get the input from the user via input box
    check for this value in the range and if it is not present in the colum insert this value in the next available cell in the range

    I need to insert a unique value in a colum

    I am getting the value from the user and then inserting it in the next available empty cell in the column

    But Before inserting I need to check that this value is previously not present in the column.

    please provide a sol.