Link to home
Start Free TrialLog in
Avatar of BurntheCure
BurntheCure

asked on

reworded: clear both form fields & external Excel cells???

I have an webpage form with textfields.  The user can enter a quantity of product they want to order.  User then clicks the submit button and that quantity automatically transfers to a Cell in an "external" Excel orderform I made. I also have a "clear" button on webform that clears ALL textfields on webform, AND also clears ALL the "quantity" Cells in my external Excel form.
****Problem Is...

If the Excel orderform "Is" Protected, I can quikly "tab" to cells that need to be filled out with customer info. But when I click the "clear" button, Nothing clears in the external Excel form probably because its protected...

if the Excel worksheet "Isnt" Protected, the "clear" button clears both webform fields & Excel orderform cells, but because Excel orderform isnt protected anymore, I must tab through each cell to enter customer info.. (which sucks)...
--------------------------------------------
Here is the script between "head tags" I'm currently using on my webpage. I also have the "clear" button in the body of the page.  Is there a way to "unprotect,clear,protect,save,close"?
I'm not too VB saavy, and this problem is driving me crazy...
--------------------------------------------
<SCRIPT LANGUAGE="VBScript">
<!--
Sub MyBtn_OnClick

Dim appXl, strFn, buf
Dim objExcelBook, objExcelSheet

strFn = "C:\Order Form.xls"

Set appXl = CreateObject("Excel.Application")
appXl.Visible = False
appXl.Workbooks.Open (strFn)
Set objExcelBook = appXl.ActiveWorkbook
Set objExcelSheet = objExcelBook.Sheets(1)

buf = Array(8900,8910,1522,1523)

Call ClearSubProc(buf, objExcelSheet)

objExcelBook.Close True
appXl.Quit
Set appXl = Nothing
Set objExcelBook = Nothing
Set objExcelSheet = Nothing
End Sub

Sub ClearSubProc(arrFind, ws)
Dim c, rngFound
For Each c In arrFind
Set rngFound = ws.Range(ws.Range("D10"), _
ws.Range("D65536").End(-4162)).Find(c,,,1)
If Not rngFound Is Nothing Then
rngFound.Offset(, -3).Resize(, 3).ClearContents
Else
MsgBox c & "is NOT Found."
End If
Next
End Sub
-->
</SCRIPT>
--------------------------------------------
If anybody can hear me out there, please help...  I do appreciate it. thank you,burnthecure
Avatar of Brian Mulder
Brian Mulder
Flag of Netherlands image

Hello BurntheCure,

could you not clear the cells in the clear button code? you say if it's protected it clears all entries

you could do something like

'unprotect
'clear entries
'protect

or not?
Avatar of BurntheCure
BurntheCure

ASKER

Sorry, not too saavy, could you explain?

Here is the script I'm currently using on my web page.  Also have the "clear" button in the body of the page.

--------------------------------------------
<SCRIPT LANGUAGE="VBScript">
<!--
Sub MyBtn_OnClick

Dim appXl, strFn, buf
Dim objExcelBook, objExcelSheet

strFn = "C:\Order Form.xls"

Set appXl = CreateObject("Excel.Application")
appXl.Visible = False
appXl.Workbooks.Open (strFn)
Set objExcelBook = appXl.ActiveWorkbook
Set objExcelSheet = objExcelBook.Sheets(1)

buf = Array(8900,8910,1522,1523)

Call ClearSubProc(buf, objExcelSheet)

objExcelBook.Close True
appXl.Quit
Set appXl = Nothing
Set objExcelBook = Nothing
Set objExcelSheet = Nothing
End Sub

Sub ClearSubProc(arrFind, ws)
Dim c, rngFound
For Each c In arrFind
Set rngFound = ws.Range(ws.Range("D10"), _
ws.Range("D65536").End(-4162)).Find(c,,,1)
If Not rngFound Is Nothing Then
rngFound.Offset(, -3).Resize(, 3).ClearContents
Else
MsgBox c & "is NOT Found."
End If
Next
End Sub
-->
</SCRIPT>
--------------------------------------------

***Remember that this code is referring to "external" Excel order form.

Thank you for any help...
Avatar of DanRollins
Hi BurntheCure,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Refund points and save as a 0-pt PAQ.

BurntheCure, Please DO NOT accept THIS comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit.  Explain why.
==========
DanRollins -- EE database cleanup volunteer
ASKER CERTIFIED SOLUTION
Avatar of SpideyMod
SpideyMod

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial