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 plotting a chart on a worksheet.
If the worksheet is unprotected it is displaying fine but If I make this worksheet as Protected it is giving error and plots the chart in a new worksheet not in the same one
How can I overcome this problem in a Protected worksheet.
Ya, I have tried but it still giving the message box showing the error.
Does this statement hangs the application ?????
Is there any other method ?????
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.
How to put a delay between the execution of Two statements in VB.
I want to put a delay of one minute between two statements.
HI dave,
not getting it how can i place the button_click proc in the standard module.
How will I capture the event then .
please give an e.g.
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()
myproc
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 dave,
I need to suspend/kill all the Proc which are scheduled by application.ontime at the click of my button STOP.
So, what code I need to include in my Stop_click() proc.
I have scheduled a Proc to run say after every 5 min. by application.ontime but I want that if in the mean while if the user click on a button all scheduled proc should be cancelled or killed .
how can I check whether there is any proc scheduled or not .
How to clear the contents of an entire sheet.
i want to delete the contents of the sheet1. without deleting the sheet.
Even this is not working Dennis.
Please help me out.
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.InnerHTMLI 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()
operation1
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()
operation1
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()
stop
end sub
now at this point I want to call operation2 through my third button
sub CommanButton3_click ()
operation2
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 .
Sachin
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
The value will be a text (String )
how can we check for the uniqueness.
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.