Printable View
Hello,
Can anybody help me as below:
1. User key in this item number E1234 the frist time
2. The second time key in E1234 for other purpose, it will auto
E1234-Revise1. It's will auto top up the revise number if the user still key in the same part for third time . I want to increase the revise number for each time user fill in same item number.How to write in VB script? Thanks
[vba] Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1" '
Please do explain as i not understand. Thanks
I was having problems with my posting this morning. It should have been this worksheet event code
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1" '<== change to suit
Static mpPrev
Dim RevNum As Long
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If mpPrev <> "" Then
If Len(mpPrev) = Len(Replace(mpPrev, "Revise", "")) Then
.Value = .Value & "-Revise1"
Else
RevNum = Right$(mpPrev, Len(mpPrev) - InStr(mpPrev, "-") - 6)
RevNum = RevNum + 1
.Value = Left$(mpPrev, InStr(mpPrev, "-")) & "Revise" & RevNum
End If
End If
mpPrev = .Value
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
[/vba]
Hi,
Thank you for your help.
Hi,
Please attach the below coding in frontpage. Then i will explain more details:
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 3</title>
</head>
<body>
<font face="Arial">
<b><font color=red>٭</font>
</TD>
<td width="16" height="26"></td>
<td bgcolor="#f4f4f4" height="26"><font size="2" face="Arial"> Part Number</font></td>
<td bgcolor="#f4f4f4" height="26">:
<input name="part" type="text" size="20" id="part" onchange="upperCase(this.id)" maxlength="10">
<script type="text/javascript">
</body>
</html>
</b></font>
<p><b><font size="2" face="Arial"> Type</font></b></td>
<td bgcolor="#f4f4f4"><!--<font face="Arial"><input name="type" size=25 value="New_Request"></font><b><font size="2" color="#0033CC" face="Arial"> </font></b></td>-->
<b><font face="Arial" size="2">:</font>
<!--<font face="Arial" size="2" color="#0000FF" onmouseover=this.style.color='red' onmouseout=this.style.color='#0000FF'> New Item</font></b>-->
<!--<input name="typedia" type="text" readonly id="typedia" value="New Item" onmouseover=this.style.color='red' onmouseout=this.style.color='#0000FF' style="font-weight: bold; color:#0000FF">-->
<select name="typedia" style="text-align: center; width:165" size="1">
<option value="">Please select type</option>
<option value="Revised Item">Revised Request</option>
<option value="New Item" selected>New Request</option>
</select></b></p>
<p><input type=submit value=" Next >> " onclick="this.disabled=true;this.value='Going to next...'; this.form.submit();"></p>
This is another file2 which continue from the above file1. Please attach new sheet in frontpage for below coding. Then i will explain more details
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>result</title>
</head>
<body>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 6.0">
<TITLE></TITLE>
</HEAD>
<body>
<FORM class="form" action="send.asp" method="Post" name=Form>
<%
set connOpen = Server.CreateObject("ADODB.connection")
SconnOpen = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("guestbooks.mdb") & ";" & _
"Persist Security Info=False"
connOpen.Open(SconnOpen)
Set rsotOpen = Server.CreateObject("ADODB.Recordset")
rsotOpen.Open "SELECT * FROM books WHERE status IN ('Open')",connOpen, 3%>
<%
response.write "<TABLE width=""120%"" bgcolor=""#F4F4F4""><TR>"
response.write "<TD width=""50%""><FONT SIZE=""3"" COLOR=""003366""> <B>OPEN REQUEST</B></FONT></TD>"
response.write "<TD width=""30%"" align=right> <font face='arial' size=2 color=black> Total Open Request: </font><b><font face='arial' size=2 color=black>"& rsotOpen.recordcount &" </font></b></TD>"
response.write "<TABLE><TR>"
rsotOpen.Close
Dim connOpen2,rsOpen,intCounter,intMod,rcolor
set connOpen2=Server.CreateObject("ADODB.Connection")
connOpen2.Provider="Microsoft.Jet.OLEDB.4.0"
connOpen2.Open(Server.Mappath("guestbooks.mdb"))
set rsOpen = Server.CreateObject("ADODB.recordset")
rsOpen.Open "SELECT * from books WHERE STATUS IN ('Open') AND author IN ('" & Session("username") &"') order by sku DESC", connOpen2
%>
<table align=center width="120%" bgcolor="#F4F4F4">
<tr><td align=right></td>
</tr>
</table>
<!--<table bgcolor="#F4F4F4" align=center width="120%" border="1" cellpadding="0" cellspacing="0" height="30" style="border-collapse: collapse" bordercolor="#006699">-->
<%if rsOpen.EOF then Response.Write "<p align=center><font face=arial size=2 color=red><b>No Open Request for "&(Session("username"))&"</b></font>"
%>
<TABLE WIDTH="52%" BORDER=1 CELLSPACING=1 CELLPADDING=1 name="rsTable" id=rsTable cols=14>
<tr>
<th align="left" bgcolor=#006699 height="13" bordercolor="#000000" bordercolorlight="#006699" bordercolordark="#006699">
<b><!--<input type="checkbox" name="C1" value="ON">-->
<A href="javascript:sortTable(0, rsTable);" title="Click ID to sort by ID">
<font color="#FFFFFF" FACE="arial" SIZE="2">ID</font><img border="0" src="combo_arrow.gif" width="8" height="8"></A></b></th>
<th align="center" bgcolor=#006699 height="13" bordercolor="#000000" bordercolorlight="#006699" bordercolordark="#006699">
<A href="javascript:sortTable(1, rsTable);" title="Click Name to sort by name">
<b><font SIZE="2" FACE="arial" color="#FFFFFF">Name</font></b>
<img border="0" src="combo_arrow.gif" width="8" height="8"></A></th>
<th align="left" bgcolor=#006699 height="13" bordercolor="#000000" bordercolorlight="#006699" bordercolordark="#006699">
<p align="center"><A href="javascript:sortTable(2, rsTable);" title="Click Topic to sort by topic">
<b><font SIZE="2" FACE="arial" color="#FFFFFF">Topic</font></b>
<img border="0" src="combo_arrow.gif" width="8" height="8"></A></th>
</tr>
<%intCounter=0%>
<%do until rsOpen.EOF%>
<%
intCounter=intCounter+1
intMod=intcounter mod 2
if intmod=0 then
rcolor = "#F4F4F4"
elseif intmod=1 then
rcolor = "#CCCCCC"
end if
%>
<tr>
<td align=left bgcolor=#FFFFFF title="Click ID to edit"><font SIZE="2" FACE="arial" COLOR="BLACK">
<width=35%>
<!--<Input Type=CheckBox Name=Delete Value=<%=rsOpen("sku")%>>-->
<span style="text-decoration: none"><font SIZE="2" FACE="arial" COLOR="BLACK">
<%if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %>
<!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">-->
<%end if%>
<%=rsOpen("sku")%><!--</a>-->1</font></span>
<td align="left" bgcolor=#FFFFFF width="216" height="20" title="REQUESTOR"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font SIZE="2" FACE="arial" COLOR="BLACK">
<span style="text-decoration: none"> User Name: Mary<%Response.Write UCASE(rsOpen("author"))%> </span></font><!--</a>-->
<td align="left" bgcolor=#FFFFFF width="211" height="20" title="TOPIC"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font face="arial" size="2">Item_Number: 1234</font></td>
<%else%>
<font SIZE="1" FACE="arial" COLOR="BLACK"><span title="This item not edited yet" style="text-decoration: none"><font color=red> N/A </font></span></font>
<%end if%></td>
<!--<td align=center bgcolor=#FFFFFF title="Edit Attachment File"><!--<a href="delete.asp?sku=<%=rsOpen("sku")%> "><img border="0" src="icon_config.gif" width="15" height="15"></a>-->
<!--<%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then
%>
<A HREF="editattachment.asp?sku=<%=rsOpen("sku")%>">
<img border="0" src="pics/editfile.gif" width="15" height="15"></A>
<%
else
%><img onClick='alert("You have no access to \nCANCEL <%=orang%> part number request.")' border="0" src="stop.gif" width="15" height="15" title="Only <%Response.Write(rsOpen("author"))%> can cancel this <%Response.Write(rsOpen("part"))%>">
<%
end if
%></td>-->
<%rsOpen.MoveNext%>
</tr>
<%loop
SET rsOpen=nothing
set connOpen2=nothing
%>
<tr>
<td align=left bgcolor=#FFFFFF title="Click ID to edit"><font SIZE="2" FACE="arial" COLOR="BLACK">
<width=35%>
<!--<Input Type=CheckBox Name=Delete Value=<%=rsOpen("sku")%>>-->
<span style="text-decoration: none">
<%if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %>
<!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">-->
<%end if%>
<%=rsOpen("sku")%><!--</a>-->2</span>
<td align="left" bgcolor=#FFFFFF width="216" height="20" title="REQUESTOR"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font SIZE="2" FACE="arial" COLOR="BLACK">
<span style="text-decoration: none"> User Name<%Response.Write UCASE(rsOpen("author"))%> </span></font><!--</a>-->:
Sindy<td align="left" bgcolor=#FFFFFF width="211" height="20" title="TOPIC"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font face="arial" size="2">Item_Number: 1234-Revised 1</font></td>
</tr>
</table>
<TABLE WIDTH="52%" BORDER=1 CELLSPACING=1 CELLPADDING=1 name="rsTable" id=table1 cols=14>
<tr>
<td align=left bgcolor=#FFFFFF title="Click ID to edit"><font SIZE="2" FACE="arial" COLOR="BLACK">
<width=35%>
<!--<Input Type=CheckBox Name=Delete Value=<%=rsOpen("sku")%>>-->
<span style="text-decoration: none">
<%if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %>
<!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">-->
<%end if%>
<%=rsOpen("sku")%><!--</a>-->3</span>
<td align="left" bgcolor=#FFFFFF width="216" height="20" title="REQUESTOR"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font SIZE="2" FACE="arial" COLOR="BLACK">
<span style="text-decoration: none"> User Name<%Response.Write UCASE(rsOpen("author"))%> </span></font><!--</a>-->:
John<td align="left" bgcolor=#FFFFFF width="211" height="20" title="TOPIC"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font face="arial" size="2">Item_Number: 8710</font></td>
</tr>
</table>
<TABLE WIDTH="52%" BORDER=1 CELLSPACING=1 CELLPADDING=1 name="rsTable" id=table2 cols=14>
<tr>
<td align=left bgcolor=#FFFFFF title="Click ID to edit"><font SIZE="2" FACE="arial" COLOR="BLACK">
<width=35%>
<!--<Input Type=CheckBox Name=Delete Value=<%=rsOpen("sku")%>>-->
<span style="text-decoration: none">
<%if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %>
<!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">-->
<%end if%>
<%=rsOpen("sku")%><!--</a>-->3</span>
<td align="left" bgcolor=#FFFFFF width="216" height="20" title="REQUESTOR"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font SIZE="2" FACE="arial" COLOR="BLACK">
<span style="text-decoration: none"> User Name<%Response.Write UCASE(rsOpen("author"))%> </span></font><!--</a>-->:
Michael<td align="left" bgcolor=#FFFFFF width="211" height="20" title="TOPIC"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font face="arial" size="2">Item_Number: 8710-Revised1</font></td>
</tr>
</table>
<TABLE WIDTH="52%" BORDER=1 CELLSPACING=1 CELLPADDING=1 name="rsTable" id=table3 cols=14>
<tr>
<td align=left bgcolor=#FFFFFF title="Click ID to edit"><font SIZE="2" FACE="arial" COLOR="BLACK">
<width=35%>
<!--<Input Type=CheckBox Name=Delete Value=<%=rsOpen("sku")%>>-->
<span style="text-decoration: none">
<%if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %>
<!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">-->
<%end if%>
<%=rsOpen("sku")%><!--</a>-->3</span>
<td align="left" bgcolor=#FFFFFF width="216" height="20" title="REQUESTOR"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font SIZE="2" FACE="arial" COLOR="BLACK">
<span style="text-decoration: none"> User Name<%Response.Write UCASE(rsOpen("author"))%> </span></font><!--</a>-->:
Peter<td align="left" bgcolor=#FFFFFF width="211" height="20" title="TOPIC"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font face="arial" size="2">Item_Number: 8710-Revised 2</font></td>
</tr>
</table>
</font>
<br>
</body>
</HTML>
</body>
</html>
Hi,
Let's me explain for file 1 and file 2.
File 1:
1. User key in item number and if the item number is same, user will select
list box Revised Request.
2. Press NEXT button
File 2
1. Display the details that fill in file1
My request: I want to count Revised Request item that is same item number fill in by user. Please see file 2.output. If same item number, it will auto revised-1. If other user still apply same item number, it will auto revised 2 and so on. Can anybody help me. Thanks