Macro that will ask for from/through dates, then run report

D

dlowery06

Board Regular
Joined
Feb 14, 2003
Messages
65
If the below html doesn't show, I can upload the file another way. What I need is a macro that will do the following for me:
When looking at the main worksheet, you see 16 macro buttons, each one runs a report. I need a macro that will start by asking for a from and a through date, then make each button go through its macro to run a report. I need one that will print the report to its own workbook, and another that will print. Example, I want to see only dates of 01/01/2003 through 01/10/2003, and the information each of the macro buttons would show. The macro buttons I've created are not date driven, only filtered by what each button shows, Pending, In Process, Jobs Completed, and Invoiced. If anyone could help, I would be pretty greatful!


<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta name="Excel Workbook Frameset">
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 9">
<link rel=File-List
href="./TEST%202003%20%20GSO%20Job%20Numbers_files/filelist.xml">
<link rel=Edit-Time-Data
href="./TEST%202003%20%20GSO%20Job%20Numbers_files/editdata.mso">
<link rel=OLE-Object-Data
href="./TEST%202003%20%20GSO%20Job%20Numbers_files/oledata.mso">
<!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:Author>Jay Statham</o:Author>
<o:LastAuthor>David Lowery</o:LastAuthor>
<o:Created>2003-02-21T13:59:04Z</o:Created>
<o:LastSaved>2003-02-24T06:33:45Z</o:LastSaved>
<o:Company>Office</o:Company>
<o:Version>9.2720</o:Version>
</o:DocumentProperties>
<o:OfficeDocumentSettings>
<o:DownloadComponents/>
<o:LocationOfComponents HRef="file:F:\msowc.cab"/>
</o:OfficeDocumentSettings>
</xml><![endif]--><![if !supportTabStrip]>
<link id="shLink" href="./TEST%202003%20%20GSO%20Job%20Numbers_files/sheet001.htm">
<link id="shLink" href="./TEST%202003%20%20GSO%20Job%20Numbers_files/sheet002.htm">
<link id="shLink" href="./TEST%202003%20%20GSO%20Job%20Numbers_files/sheet003.htm">

<link id="shLink">

<script language="JavaScript">
<!--
var c_lTabs=3;

var c_rgszSh=new Array(c_lTabs);
c_rgszSh[0] = "After Disaster";
c_rgszSh[1] = "GSO In Process 2003";
c_rgszSh[2] = "GSO In Process";



var c_rgszClr=new Array(8);
c_rgszClr[0]="window";
c_rgszClr[1]="buttonface";
c_rgszClr[2]="windowframe";
c_rgszClr[3]="windowtext";
c_rgszClr[4]="threedlightshadow";
c_rgszClr[5]="threedhighlight";
c_rgszClr[6]="threeddarkshadow";
c_rgszClr[7]="threedshadow";

var g_iShCur;
var g_rglTabX=new Array(c_lTabs);

function fnGetIEVer()
{
var ua=window.navigator.userAgent
var msie=ua.indexOf("MSIE")
if (msie>0 && window.navigator.platform=="Win32")
return parseInt(ua.substring(msie+5,ua.indexOf(".", msie)));
else
return 0;
}

function fnBuildFrameset()
{
var szHTML="<frameset rows=\"*,18\" border=0 width=0 frameborder=no framespacing=0>"+
"<frame src=\""+document.all.item("shLink")[0].href+"\" name=\"frSheet\" noresize>"+
"<frameset cols=\"54,*\" border=0 width=0 frameborder=no framespacing=0>"+
"<frame src=\"\" name=\"frScroll\" marginwidth=0 marginheight=0 scrolling=no>"+
"<frame src=\"\" name=\"frTabs\" marginwidth=0 marginheight=0 scrolling=no>"+
"</frameset></frameset><plaintext>";

with (document) {
open("text/html","replace");
write(szHTML);
close();
}

fnBuildTabStrip();
}

function fnBuildTabStrip()
{
var szHTML=
"<html><head><style>.clScroll {font:8pt Courier New;color:"+c_rgszClr[6]+";cursor:default;line-height:10pt;}"+
".clScroll2 {font:10pt Arial;color:"+c_rgszClr[6]+";cursor:default;line-height:11pt;}</style></head>"+
"<body onclick=\"event.returnValue=false;\" ondragstart=\"event.returnValue=false;\" onselectstart=\"event.returnValue=false;\" bgcolor="+c_rgszClr[4]+" topmargin=0 leftmargin=0><table cellpadding=0 cellspacing=0 width=100%>"+
"<tr><td colspan=6 height=1 bgcolor="+c_rgszClr[2]+"></td></tr>"+
"<tr><td style=\"font:1pt\">&<td>"+
"<td valign=top id=tdScroll class=\"clScroll\" onclick=\"parent.fnFastScrollTabs(0);\" onmouseover=\"parent.fnMouseOverScroll(0);\" onmouseout=\"parent.fnMouseOutScroll(0);\"><a>«</a></td>"+
"<td valign=top id=tdScroll class=\"clScroll2\" onclick=\"parent.fnScrollTabs(0);\" ondblclick=\"parent.fnScrollTabs(0);\" onmouseover=\"parent.fnMouseOverScroll(1);\" onmouseout=\"parent.fnMouseOutScroll(1);\"><a>&lt</a></td>"+
"<td valign=top id=tdScroll class=\"clScroll2\" onclick=\"parent.fnScrollTabs(1);\" ondblclick=\"parent.fnScrollTabs(1);\" onmouseover=\"parent.fnMouseOverScroll(2);\" onmouseout=\"parent.fnMouseOutScroll(2);\"><a>&gt</a></td>"+
"<td valign=top id=tdScroll class=\"clScroll\" onclick=\"parent.fnFastScrollTabs(1);\" onmouseover=\"parent.fnMouseOverScroll(3);\" onmouseout=\"parent.fnMouseOutScroll(3);\"><a>»</a></td>"+
"<td style=\"font:1pt\">&<td></tr></table></body></html>";

with (frames['frScroll'].document) {
open("text/html","replace");
write(szHTML);
close();
}

szHTML =
"<html><head>"+
"<style>A:link,A:visited,A:active {text-decoration:none;"+"color:"+c_rgszClr[3]+";}"+
".clTab {cursor:hand;background:"+c_rgszClr[1]+";font:8pt Arial;padding-left:3px;padding-right:3px;text-align:center;}"+
".clBorder {background:"+c_rgszClr[2]+";font:1pt;}"+
"</style></head><body onload=\"parent.fnInit();\" onselectstart=\"event.returnValue=false;\" ondragstart=\"event.returnValue=false;\" bgcolor="+c_rgszClr[4]+
" topmargin=0 leftmargin=0><table id=tbTabs cellpadding=0 cellspacing=0>";

var iCellCount=(c_lTabs+1)*2;

var i;
for (i=0;i<iCellCount;i+=2)
szHTML+="<col width=1><col>";

var iRow;
for (iRow=0;iRow<6;iRow++) {

szHTML+="<tr>";

if (iRow==5)
szHTML+="<td colspan="+iCellCount+"></td>";
else {
if (iRow==0) {
for(i=0;i<iCellCount;i++)
szHTML+="<td height=1 class=\"clBorder\"></td>";
} else if (iRow==1) {
for(i=0;i<c_lTabs;i++) {
szHTML+="<td height=1 nowrap class=\"clBorder\">&</td>";
szHTML+=
"<td id=tdTab height=1 nowrap class=\"clTab\" onmouseover=\"parent.fnMouseOverTab("+i+");\" onmouseout=\"parent.fnMouseOutTab("+i+");\">"+
"<a href=\""+document.all.item("shLink").href+"\" target=\"frSheet\" id=aTab>&"+c_rgszSh+"&</a></td>";
}
szHTML+="<td id=tdTab height=1 nowrap class=\"clBorder\"><a id=aTab>&</a></td><td width=100%></td>";
} else if (iRow==2) {
for (i=0;i<c_lTabs;i++)
szHTML+="<td height=1></td><td height=1 class=\"clBorder\"></td>";
szHTML+="<td height=1></td><td height=1></td>";
} else if (iRow==3) {
for (i=0;i<iCellCount;i++)
szHTML+="<td height=1></td>";
} else if (iRow==4) {
for (i=0;i<c_lTabs;i++)
szHTML+="<td height=1 width=1></td><td height=1></td>";
szHTML+="<td height=1 width=1></td><td></td>";
}
}
szHTML+="</tr>";
}

szHTML+="</table></body></html>";
with (frames['frTabs'].document) {
open("text/html","replace");
charset=document.charset;
write(szHTML);
close();
}
}

function fnInit()
{
g_rglTabX[0]=0;
var i;
for (i=1;i<=c_lTabs;i++)
with (frames['frTabs'].document.all.tbTabs.rows[1].cells[fnTabToCol(i-1)])
g_rglTabX=offsetLeft+offsetWidth-6;
}

function fnTabToCol(iTab)
{
return 2*iTab+1;
}

function fnNextTab(fDir)
{
var iNextTab=-1;
var i;

with (frames['frTabs'].document.body) {
if (fDir==0) {
if (scrollLeft>0) {
for (i=0;i<c_lTabs&&g_rglTabX<scrollLeft;i++);
if (i<c_lTabs)
iNextTab=i-1;
}
} else {
if (g_rglTabX[c_lTabs]+6>offsetWidth+scrollLeft) {
for (i=0;i<c_lTabs&&g_rglTabX<=scrollLeft;i++);
if (i<c_lTabs)
iNextTab=i;
}
}
}
return iNextTab;
}

function fnScrollTabs(fDir)
{
var iNextTab=fnNextTab(fDir);

if (iNextTab>=0) {
frames['frTabs'].scroll(g_rglTabX[iNextTab],0);
return true;
} else
return false;
}

function fnFastScrollTabs(fDir)
{
if (c_lTabs>16)
frames['frTabs'].scroll(g_rglTabX[fDir?c_lTabs-1:1],0);
else
if (fnScrollTabs(fDir)>0) window.setTimeout("fnFastScrollTabs("+fDir+");",5);
}

function fnSetTabProps(iTab,fActive)
{
var iCol=fnTabToCol(iTab);
var i;

if (iTab>=0) {
with (frames['frTabs'].document.all) {
with (tbTabs) {
for (i=0;i<=4;i++) {
with (rows) {
if (i==0)
cells[iCol].style.background=c_rgszClr[fActive?0:2];
else if (i>0 && i<4) {
if (fActive) {
cells[iCol-1].style.background=c_rgszClr[2];
cells[iCol].style.background=c_rgszClr[0];
cells[iCol+1].style.background=c_rgszClr[2];
} else {
if (i==1) {
cells[iCol-1].style.background=c_rgszClr[2];
cells[iCol].style.background=c_rgszClr[1];
cells[iCol+1].style.background=c_rgszClr[2];
} else {
cells[iCol-1].style.background=c_rgszClr[4];
cells[iCol].style.background=c_rgszClr[(i==2)?2:4];
cells[iCol+1].style.background=c_rgszClr[4];
}
}
} else
cells[iCol].style.background=c_rgszClr[fActive?2:4];
}
}
}
with (aTab[iTab].style) {
cursor=(fActive?"default":"hand");
color=c_rgszClr[3];
}
}
}
}

function fnMouseOverScroll(iCtl)
{
frames['frScroll'].document.all.tdScroll[iCtl].style.color=c_rgszClr[7];
}

function fnMouseOutScroll(iCtl)
{
frames['frScroll'].document.all.tdScroll[iCtl].style.color=c_rgszClr[6];
}

function fnMouseOverTab(iTab)
{
if (iTab!=g_iShCur) {
var iCol=fnTabToCol(iTab);
with (frames['frTabs'].document.all) {
tdTab[iTab].style.background=c_rgszClr[5];
}
}
}

function fnMouseOutTab(iTab)
{
if (iTab>=0) {
var elFrom=frames['frTabs'].event.srcElement;
var elTo=frames['frTabs'].event.toElement;

if ((!elTo) ||
(elFrom.tagName==elTo.tagName) ||
(elTo.tagName=="A" && elTo.parentElement!=elFrom) ||
(elFrom.tagName=="A" && elFrom.parentElement!=elTo)) {

if (iTab!=g_iShCur) {
with (frames['frTabs'].document.all) {
tdTab[iTab].style.background=c_rgszClr[1];
}
}
}
}
}

function fnSetActiveSheet(iSh)
{
if (iSh!=g_iShCur) {
fnSetTabProps(g_iShCur,false);
fnSetTabProps(iSh,true);
g_iShCur=iSh;
}
}

window.g_iIEVer=fnGetIEVer();
if (window.g_iIEVer>=4)
fnBuildFrameset();
//-->
</script>
<![endif]><!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>After Disaster</x:Name>
<x:WorksheetSource
HRef="./TEST%202003%20%20GSO%20Job%20Numbers_files/sheet001.htm"/>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>GSO In Process 2003</x:Name>
<x:WorksheetSource
HRef="./TEST%202003%20%20GSO%20Job%20Numbers_files/sheet002.htm"/>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>GSO In Process</x:Name>
<x:WorksheetSource
HRef="./TEST%202003%20%20GSO%20Job%20Numbers_files/sheet003.htm"/>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:Stylesheet
HRef="./TEST%202003%20%20GSO%20Job%20Numbers_files/stylesheet.css"/>
<x:WindowHeight>6225</x:WindowHeight>
<x:WindowWidth>11340</x:WindowWidth>
<x:WindowTopX>450</x:WindowTopX>
<x:WindowTopY>1080</x:WindowTopY>
<x:AcceptLabelsInFormulas/>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
<x:ExcelName>
<x:Name>_FilterDatabase</x:Name>
<x:Hidden/>
<x:SheetIndex>2</x:SheetIndex>
<x:Formula>='GSO In Process 2003'!$S$1:$S$27</x:Formula>
</x:ExcelName>
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="3073"/>
</xml><![endif]-->
</head>

<frameset rows="*,39" border=0 width=0 frameborder=no framespacing=0>
<frame src="./TEST%202003%20%20GSO%20Job%20Numbers_files/sheet001.htm" name="frSheet">
<frame src="./TEST%202003%20%20GSO%20Job%20Numbers_files/tabstrip.htm" name="frTabs" marginwidth=0 marginheight=0>
<noframes>
<body>


This page uses frames, but your browser doesn't support them.</p>
</body>
</noframes>
</frameset>
</html>
 
Sort by date Sort by votes
Hi dlowery06:

Well, the simulation does not show. However, have you written part of the macro, in which you want to add prompting for the dates? if you have something written, why don't you post that. If you don't have any thing written, then you will have to provide for a clear description of where the dates would be recorded and how would these be coordinated with the buttons you have created.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Comparing rows in two spreadsheets & Finding close match
Replies
1
Views
590
ravishankar
R
S
  • Question Question
Complex Lookup Function
Replies
7
Views
854
share_knowledge
S
O
  • Question Question
Excel Web Query from JavaScript Source
Replies
0
Views
3K
oddball508
O
C
  • Question Question
Java pop up - how to "press" the OK button??
Replies
4
Views
934
NateO
NateO

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top