VBA Forum RSS Feed (Replies Included)

Copy\Paste

Mon, 13 May 2013 11:25:00 -0700

I could use some help with the code that I have put together.
The problem I am having is with the copy and paste or really with just the paste.

Here is how things should work:
1. I would open a worksheet and find the Cross Reference information in that sheet that I would like copy.
2. I would then run my code and get a message box saying (Do you want to copy?) (Yes\No)
If yes a box will appear saying (Copy Area). I would then select the area on the spreadsheet that I would like
to copy. Select Ok
3. Next another textbox will appear asking me to enter the Cross Ref# that I would like to search for. I enter the
number and the search will begin. The search would be on the active workbook and be asked to search the
directory for other workbooks for the Cross Ref#. This all works fine.

My Issue:

Here is my issue and what I am looking for the code to do.
Every time that the Cross Reference # is found in my search I would like the option to paste the information or not paste the information (Yes/No). But that the search continues till no value is found.
Which means it will continue to search the workbook as well as the active directory opening up workbooks searching for the Cross Ref#
The code works fine in copying the information. As well as searching for the information. It just I cannot get it to paste that information or not paste the information.

I am not a Programmer. So I have been working on this for a long while. Your help would be great!!!

Option Explicit
    Sub Copy_2()
    Dim ws     As Worksheet
    Dim rFound As Range
    Dim strName As String
    Dim doyou  As String
    Dim docopy$
    Dim xArea  As Range
    Dim eArea  As Range
    Dim xData  As Workbook
    Dim xName$, ePath$
    Dim fPicker As Object
    Dim bsearch$
     With Application
         '    .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With
docopy = MsgBox("Do you want to copy?", vbYesNo)
                If docopy = vbYes Then
                    Set xArea = Application.InputBox(prompt:="Copy Area", title:="Select Range", Type:=8)
                    Rem any select any worksheet And cell
       strName = InputBox("Enter Cross Ref#")
    If strName = "" Then Exit Sub
    For Each ws In Worksheets
        With ws.UsedRange
         ActiveWindow.ScrollColumn = 1
         ActiveWindow.SmallScroll ToLeft:=1
            Set rFound = .Find(what:=strName, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
            If Not rFound Is Nothing Then
                Application.Goto rFound, True
                 ActiveWindow.ScrollColumn = 1
                 ActiveWindow.SmallScroll ToLeft:=1
                doyou = MsgBox("Do you want to continue searching?", vbYesNo)
                If doyou = vbNo Then Exit Sub
       
            End If
        End With
    Next ws
    MsgBox "Value not found"
    End If
    doyou = MsgBox("Do you want to Search Directory?", vbYesNo)
     If doyou = vbNo Then Exit Sub
      Do
        Set fPicker = Application.FileDialog(msoFileDialogFolderPicker)
        With fPicker
            .Show
            ePath = .SelectedItems(1) & "\"
        End With
        xName = Dir(ePath & "*.xls*")
        Do While Len(xName) > 0
            Set xData = Workbooks.Open(ePath & xName)
            For Each ws In Worksheets
        With ws.UsedRange
        ActiveWindow.ScrollColumn = 1
        ActiveWindow.SmallScroll ToLeft:=1
            Set rFound = .Find(what:=strName, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
            If strName = "" Then Exit Sub
            If Not rFound Is Nothing Then
                Application.Goto rFound, True
                 ActiveWindow.ScrollColumn = 1
                 ActiveWindow.SmallScroll ToLeft:=1
                doyou = MsgBox("Do you want to continue searching?", vbYesNo)
                If doyou = vbNo Then Exit Sub
       
            End If
       
                End With
            Next ws
            xData.Close False
                    xName = Dir
        Loop
        bsearch = MsgBox("Value not found, do you want to search another directory?", vbYesNo)
    Loop Until bsearch = vbNo
    With Application
         ' .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
  
        End With
    
    MsgBox "Value not found"
End Sub

Re: PPT VBA - Assign text box value to variable

Fri, 22 Mar 2013 16:47:15 -0700

Hello

You've probably already worked this out but just in case here's my suggestion. Since I don't know what code you've written and how you've set up your forms and I haven't worked with PowerPoint VBA forms (just Word, Excel, and Access) I'm making the assumption that PowerPoint isn't any different.

' frmFinalJeopardy - User form that stores the "wager" field.

' In the main body of your code Load the User Form
Load frmFinalJeopardy
' If you know the Max Amount they can wager you can assign it to the field before you display the form to the user.
frmFinalJeopardy.wager = curMaxAvailable

' Display the form to the user
frmFinalJeopardy.Show

' Once the user has entered their wager and hit a command button on the form you can hide the form from the user but it stays in memory so you can still access the data on the form.
frmFinalJeopardy.Hide

' At this point you can check the value and make corrections (i.e., if they entered a negative number or more than they have to wager). You can use the same process above to change the value of the wager field, display an error message, and Show the form again so the user can make corrections.

' Assign the value from the wager field that's on the frmFinalJeopardy form to the finalwager variable.

'Val function will take the fields numeric value. If the user entered nothing or entered text it will 'display as 0.

'Format is just there to display the value as currency.
finalwager = Format(Val(frmFinalJeopardy.wager), "$#,000,000.00")

' Remove the form from memory
Unload frmFinalJeopardy

Hope that helps.

Bill

Re: PPT VBA - Assign text box value to variable

Fri, 22 Mar 2013 16:44:29 -0700

PPT VBA - Assign text box value to variable

Sun, 17 Mar 2013 09:52:58 -0700

Hi there,

I'm fairly new to VBA, so I am not sure this is possible, but it seems like it should be... I am working on a Jeopardy project. I found a free template with scoring online, but there was no Final Jeopardy option, so I've been trying to code that in. The other scoring is fairly straightfoward - objects have been assigned macros which add 100, 200, or 300 points to the score variable.

For Final Jeopardy, the competitor (there's only one, so no need to worry about multiple teams for this) needs to be able to enter any point value. I created an ActiveX texbox control named "wager" in which this value could be input. I have defined a variable (Dim finalwager as long) for the final wager output. The issue is that I can't seem to find the right code to assign the value in the wager input box to finalwager.

I have a sample of the presentation available (3MB - too big to attach). It's a last-minute project, and the game is March 18 (tomorrow, joy) - I've been researching this problem for 3 days now with no luck. Thanks in advance for any assistance.

RightFax Utility- Open and Print all Incoming Faxes

Tue, 19 Feb 2013 06:58:51 -0700

I know some VBA but I do not know any other languages. There may be other languages that are better suited to help me with my problem. I am open to suggestions and always eager to learn more.

Here is the issue at hand:
My boss receives large groups of faxes via RightFax Utility. Currently she opens each of the incoming faxes one by one in order to print them. I am curious, is there a way to print all incoming faxes at once?

Advice is very appreciated.

Thank you,
Anna

RightFax Utility- Open and Print all Incoming Faxes

Tue, 19 Feb 2013 06:57:00 -0700

VBA code to divide numbers for percentages

Fri, 01 Feb 2013 11:18:44 -0700

I'm using Access 2007. I have VBA code that takes a budget amount as a currency veriable and subtracts from it a sum of expenditure YTD currency variable, which works fine resulting with a budget balance remaining. I tried adding code to divide the sum variable by the budget variable, but the percent that is returned is 0.00% My table fields are currency and the form field is formated to percent. Any help to return the actual percent would be greatly appreciated.

Private Sub cmdOpenBalance_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryExpendStnd"
DoCmd.SetWarnings True
Dim strHoldYear As String
Dim curHoldRent As Currency
Dim curHoldFurn As Currency
Dim curHoldSecD As Currency
Dim curHoldRehb As Currency
Dim curHoldCont As Currency
Dim curSumRent As Currency
Dim curSumFurn As Currency
Dim curSumSecD As Currency
Dim curSumRehb As Currency
Dim curSumCont As Currency
DoCmd.OpenForm "frmBudget"
strHoldYear = Forms![frmBudget]![BudgetYear]
curHoldRent = Forms![frmBudget]![RentStipends]
curHoldFurn = Forms![frmBudget]![FurnitureHoushld]
curHoldSecD = Forms![frmBudget]![SecDeposit]
curHoldRehb = Forms![frmBudget]![Rehab]
curHoldCont = Forms![frmBudget]![Contingency]
DoCmd.Close acForm, "frmBudget"
DoCmd.OpenForm "frmSumRent"
curSumRent = Forms![frmSumRent]![SumOfAmount1]
DoCmd.Close acForm, "frmSumRent"
DoCmd.OpenForm "frmSumFurnHoushld"
curSumFurn = Forms![frmSumFurnHoushld]![SumOfAmount1]
DoCmd.Close acForm, "frmSumFurnHoushld"
DoCmd.OpenForm "frmSumSecDep"
curSumSecD = Forms![frmSumSecDep]![SumOfAmount1]
DoCmd.Close acForm, "frmSumSecDep"
DoCmd.OpenForm "frmSumRehab"
curSumRehb = Forms![frmSumRehab]![SumOfAmount1]
DoCmd.Close acForm, "frmSumRehab"
DoCmd.OpenForm "frmSumContin"
curSumCont = Forms![frmSumContin]![SumOfAmount1]
DoCmd.Close acForm, "frmSumContin"
DoCmd.OpenForm "frmBalance"
Forms![frmBalance]![BudgetYear] = strHoldYear
Forms![frmBalance]![RentStipends] = curHoldRent - curSumRent
Forms![frmBalance]![RentPercent] = curSumRent / curHoldRent
Forms![frmBalance]![FurnitureHoushld] = curHoldFurn - curSumFurn
Forms![frmBalance]![FurnPercent] = curSumFurn / curHoldFurn
Forms![frmBalance]![SecDeposit] = curHoldSecD - curSumSecD
Forms![frmBalance]![SecDPercent] = curSumSecD / curHoldSecD
Forms![frmBalance]![Rehab] = curHoldRehb - curSumRehb
Forms![frmBalance]![RehPercent] = curSumRehb / curHoldRehb
Forms![frmBalance]![Contingency] = curHoldCont - curSumCont
Forms![frmBalance]![ContinPercent] = curSumCont / curHoldCont


End Sub

VBA code to divide numbers for percentages

Fri, 01 Feb 2013 11:16:54 -0700

Private Sub cmdOpenBalance_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryExpendStnd"
DoCmd.SetWarnings True
Dim strHoldYear As String
Dim curHoldRent As Currency
Dim curHoldFurn As Currency
Dim curHoldSecD As Currency
Dim curHoldRehb As Currency
Dim curHoldCont As Currency
Dim curSumRent As Currency
Dim curSumFurn As Currency
Dim curSumSecD As Currency
Dim curSumRehb As Currency
Dim curSumCont As Currency
DoCmd.OpenForm "frmBudget"
strHoldYear = Forms![frmBudget]![BudgetYear]
curHoldRent = Forms![frmBudget]![RentStipends]
curHoldFurn = Forms![frmBudget]![FurnitureHoushld]
curHoldSecD = Forms![frmBudget]![SecDeposit]
curHoldRehb = Forms![frmBudget]![Rehab]
curHoldCont = Forms![frmBudget]![Contingency]
DoCmd.Close acForm, "frmBudget"
DoCmd.OpenForm "frmSumRent"
curSumRent = Forms![frmSumRent]![SumOfAmount1]
DoCmd.Close acForm, "frmSumRent"
DoCmd.OpenForm "frmSumFurnHoushld"
curSumFurn = Forms![frmSumFurnHoushld]![SumOfAmount1]
DoCmd.Close acForm, "frmSumFurnHoushld"
DoCmd.OpenForm "frmSumSecDep"
curSumSecD = Forms![frmSumSecDep]![SumOfAmount1]
DoCmd.Close acForm, "frmSumSecDep"
DoCmd.OpenForm "frmSumRehab"
curSumRehb = Forms![frmSumRehab]![SumOfAmount1]
DoCmd.Close acForm, "frmSumRehab"
DoCmd.OpenForm "frmSumContin"
curSumCont = Forms![frmSumContin]![SumOfAmount1]
DoCmd.Close acForm, "frmSumContin"
DoCmd.OpenForm "frmBalance"
Forms![frmBalance]![BudgetYear] = strHoldYear
Forms![frmBalance]![RentStipends] = curHoldRent - curSumRent
Forms![frmBalance]![RentPercent] = curSumRent / curHoldRent
Forms![frmBalance]![FurnitureHoushld] = curHoldFurn - curSumFurn
Forms![frmBalance]![FurnPercent] = curSumFurn / curHoldFurn
Forms![frmBalance]![SecDeposit] = curHoldSecD - curSumSecD
Forms![frmBalance]![SecDPercent] = curSumSecD / curHoldSecD
Forms![frmBalance]![Rehab] = curHoldRehb - curSumRehb
Forms![frmBalance]![RehPercent] = curSumRehb / curHoldRehb
Forms![frmBalance]![Contingency] = curHoldCont - curSumCont
Forms![frmBalance]![ContinPercent] = curSumCont / curHoldCont


End Sub

Re: Commenting out segments

Tue, 22 Jan 2013 23:24:14 -0700

Select the correct materials. Various stuffs carry varied functions. With regards to selecting a plus dimension wedding ceremony gown, taffeta and satin are two of your hottest options due to fantastic elasticity. Also, these supplies appear fantastic with ruffle-a fairly accessory for nearly every plus size wedding dresses.It' s much better to pick a floor-length a-line fashion. A-line wedding dresses is well-known to become figure-flattering. Lengthy fashion definitely does much better in generating your figure slender.

Referencing Another Worksheet Dynamically

Fri, 11 Jan 2013 12:56:52 -0700

Hey everyone I'll try and keep this short. Basically I'm trying to create a Macro that wil create a summary sheet that I can re-use multiple times and I'm having trouble referencing the cells in the other workbooks.

I've bolded the particular bit of code giving me trouble:
If x = 1 Then N = N - 1
For i = 2 To N
With Sheets("Summary Sheet")
shtName = Sheets(i).Name
'Add a hyperlink to A1 of each sheet.
.Range("B" & nRow).Hyperlinks.Add _
Anchor:=.Range("B" & nRow), Address:="#'" & _
shtName & "'!A1", TextToDisplay:=shtName
.Range("B" & nRow).HorizontalAlignment = xlLeft
.Range("A" & nRow).Value = nRow - 4
.Range("C" & nRow).Value = "='shtName'!$D$5"
nRow = nRow + 1
End With

If you were to click on a cell in the 'C' column I want it to read "='WhatEverThisParticularSheetsNameIs'!$D$5 . My problem is that I'm using a variable I've defined as 'shtName' instead of going through and writting in each seperate sheets name. When I include shtName in the quotes it puts it as plane text rather than using the variable.

Any help would be appreciated! Keep in mind I'm pretty bad at VBA haha.

Referencing Another Worksheet Dynamically

Fri, 11 Jan 2013 12:55:02 -0700

copying files from one folder to another

Wed, 26 Dec 2012 13:10:43 -0700

I need ideas for the code for copying all *.xlsx files from one folder to another.

I have all my code for working all my processes correctly and it works almost too smooth. (I keep expecting it to hiccup somewhere, but it hasn't so far). I've already changed the focus to the correct folder on a network drive, now to complete the automation process, I need to copy the *.xlsx files from one folder to another.

Anyone have any suggestions?

Thanks for the help!!!

copying files from one folder to another

Wed, 26 Dec 2012 12:28:21 -0700

VBA code for

Wed, 26 Dec 2012 12:18:35 -0700

I need the code for copying all *.xlsx files from 1 folder to another.

I have all my code for working all my processes correctly and it works almost too smooth. (I keep expecting it to hiccup somewhere, but it hasn't so far). Now, to complete the automation process, I need to copy the *.xlsx files from one folder to another.

Anyone have any suggestions?

Thanks for the help!!!

VBA code for

Wed, 26 Dec 2012 12:16:46 -0700

Problems with fortran library

Mon, 10 Dec 2012 12:24:20 -0700

Hello!!!
I am working with libraries to VBA Excel 2010 (xlsm format), but I have problems in the excel ouputs. This is my script:
* fortran 11
Subroutine FortranDLL( Array1, upbound )
Implicit None
!DEC$ ATTRIBUTES STDCALL, REFERENCE, DLLEXPORT :: FortranDLL
Integer :: upbound
Integer :: Array1(1:upbound)

Integer :: i
do i=1,upbound
Array1(i)=288.16-0.0065*Array1(i)
end do
End Subroutine FortranDLL

* visual basic macro

Option Explicit
Declare Sub fortrandll Lib "fortrandll.dll" (ByRef Array1 As Double, ByRef upbound As Long)

Sub Button1_Click()

Dim II As Long
Dim test(10) As Double

ChDrive (ThisWorkbook.Path)
ChDir (ThisWorkbook.Path)

II = 11
Call fortrandll(test(1), II)
Range("a1").Value = test(1)
Range("a2").Value = test(2)
Range("a3").Value = test(3)
Range("a4").Value = test(4)
Range("a5").Value = test(5)
Range("a6").Value = test(6)
Range("a7").Value = test(7)
Range("a8").Value = test(8)
Range("a9").Value = test(9)
Range("a10").Value = test(10)
End Sub

The problem is that the output is “0” and it isn’t the solution.
Someone know what is wrong?
What is the solution?

Tank!!!!!

Problems with fortran library

Mon, 10 Dec 2012 12:22:28 -0700

DeleteFile VBA Attachmate Reflections

Fri, 07 Dec 2012 18:39:42 -0700

I am trying to delete the first line of text file using Attachmate Reflections 2011 VBA script. But the code does not work. How can I use "DeleteLines 1, 1"? any help would be appreciated.

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("C:\Batch_Manipulator_File.txt", ForWriting, TristateFalse)

f.Write "Hello world!"
f.DeleteLines 1, 1
f.Close

fs.CopyFile "C:\Temp.txt", True

DeleteFile VBA Attachmate Reflections

Fri, 07 Dec 2012 18:37:00 -0700

Problems with ActiveWorkbook.XmlImport & ImportMap

Wed, 28 Nov 2012 15:36:21 -0700

I've been working on a script that auto-creates a new *.xlsx workbook, imports the XSD map, and imports an XML file. I've just about worked out the bugs to have it use the new file and format the data as needed, then it filters out data based on vendor (I have a list of 52 vendors - and this part works beautifully), dumps the mined data to a new workbook, saves, closes, and continues.

The problem I'm having is the map does not auto-import. It fails to import the map and data.

Sub openxmlfile()
    Workbooks.Add
    ActiveWorkbook.XmlMaps.Add("\\Automation files\VendorDetails.xsd" _
        , "vendordetails").Name = "vendordetails_Map"
    ActiveWorkbook.XmlImport = ("\\VendPaymtDtl_12112621482978.xml")
End Sub
Public Sub VendorRecon()
'
'   This section prepares the yyyy Mmm - Vendor Recon File.xlsx
'
    Call openxmlfile
    Call borders
    Call SSN
    Call USD
    Call dates
    Call FName
    Call LName
    Call freeze
    Call rename_worksheet
    Call save
'
'   This section begins the creation of each vendor's *.xlsx recon file
'
    Call vendor_001
    Call copy
    Call create_new
    Call paste
    Call fit_hide
    Call page_format
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftFooter = "(001) Ada County"
    End With
    Application.PrintCommunication = True
    Call page_fit
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "2012 Dec - (001) Vendor Recon"
    Call freeze
    ChDir "Y:\2012 Dec\Vendor Recon Files\My Recon Files"
    ActiveWorkbook.SaveAs Filename:= _
        "Y:\2012 Dec\Vendor Recon Files\My Recon Files\2012 Dec - (001) Vendor Recon File.xlsx" _
        , FileFormat:=51, Password:="x", WriteResPassword:="x", CreateBackup:=False
    Call close_window
'
    Call vendor_040
    .
    .
    .
End Sub

Here's my XSD Map:

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema xmlns:sql="urn:schemas-microsoft-com:mapping-schema" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" >
  <xs:complexType name="PremiumReconType" >
    <xs:sequence>
      <xs:element minOccurs="1" maxOccurs="1" name="vendordetails" type="dateheader" />
      <xs:element minOccurs="0" maxOccurs="unbounded" name="payment" type="paymentmade" />
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="dateheader" >
    <xs:attribute name="datetime" type="xs:date" />
  </xs:complexType>
  <xs:complexType name="paymentmade" >
    <xs:attribute name="run_date" type="xs:date" />
    <xs:attribute name="payroll_cycle" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="0" />
          <xs:maxLength value="30" />
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="pay_eff_date" type="xs:date" />
    <xs:attribute name="vendor_name" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="0" />
          <xs:maxLength value="30" />
        </xs:restriction>
      </xs:simpleType>
	</xs:attribute>  
    <xs:attribute name="address_lines" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="0" />
          <xs:maxLength value="30" />
        </xs:restriction>
      </xs:simpleType>
	</xs:attribute>  
    <xs:attribute name="vendor_city_state_zip" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="0" />
          <xs:maxLength value="30" />
        </xs:restriction>
      </xs:simpleType>
	</xs:attribute>  
    <xs:attribute name="country" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="0" />
          <xs:maxLength value="30" />
        </xs:restriction>
      </xs:simpleType>
	</xs:attribute>  
    <xs:attribute name="ssn" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="11" />
          <xs:maxLength value="11" />
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
	  <xs:attribute name="FirstName" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="1" />
          <xs:maxLength value="30" />
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="MiddleName" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="0" />
          <xs:maxLength value="30" />
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="LastName" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="1" />
          <xs:maxLength value="50" />
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="employer_num" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="1" />
          <xs:maxLength value="4" />
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="vendor_code" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="1" />
          <xs:maxLength value="10" />
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="status" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="1" />
          <xs:maxLength value="20" />
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="ongoing" type="xs:decimal" />
    <xs:attribute name="adjustment" type="xs:decimal" />
    <xs:attribute name="funding_code" >
      <xs:simpleType>
        <xs:restriction base="xs:int">
          <xs:totalDigits value="4" />
          <xs:fractionDigits value="0" />
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="plan_name" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="1" />
          <xs:maxLength value="30" />
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="plan_number" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="1" />
          <xs:maxLength value="30" />
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="policy_number" >
      <xs:simpleType>
        <xs:restriction base="xs:string" >
          <xs:minLength value="1" />
          <xs:maxLength value="30" />
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
	</xs:complexType>
  <xs:element name="vendordetails" type="PremiumReconType" />
</xs:schema>

Excel VBA - Dynamically Adding a Page to a Multi-Page UserForm

Wed, 28 Nov 2012 08:18:02 -0700

I have a multi-page userform. Currently, it’s three pages. I need to create a field where the user can input a number and it will do the following:

1. Duplicate page 3 of the Multi-Page UserForm that many times.
2. Duplicate the excel worksheet that same number of times.

I have found the following code that works for part of Step 2. But, I still need to work out having it repeat as many times as requested by the user.

Sub CopyWorksheet()
    Sheets("Candidate (1)").Select
    Sheets("Candidate").Copy After:=Sheets(Sheets.Count)
    Sheets("Values").Select
    Sheets("Values").Move After:=Sheets(Sheets.Count)
End Sub

I have found online the following code should duplicate a page, but it’s not working.

    Set M = IntvwWorksheet.Pages.Add("Candidate2", "Candidate 2", 4)

Can someone please help me with this. I have nearly finished everything about this UserForm except this part and I desperately need to get it figured out.

I appreciate your help!

Bayser Consulting - Consultant position available (Excel/Access VBA)

Thu, 08 Nov 2012 13:35:40 -0700

Ask yourself the following:

1. Do you have an analytical mind and feel you are not fully using your smarts to solve real-life problems?

2. Do you enjoy a fast-paced environment and feel energized when sharp colleagues poke holes in your claims?

3. Are you good with the computer and can do Excel/VBA, Access, and SAS in your dream?

4. Are you a good listener and does your response factor in your audience’s position?

5. Are you articulate and put together presentations that register with the audience for clarity and logic?

6. Are you gung-ho on succeeding even though this means working very hard?

7. Does your long-term vision take you in leadership position?

8. Are your personable and friendly?

If you answered yes to all questions, you have the profile we are looking for immediate hire at a competitive salary and great healthcare coverage.

If you happen to have a PhD in math, physics or engineering, you are even more interesting. Prior exposure to business/consulting (e.g. MBA) a plus.

Bayser Consulting - Consultant position available (Excel/Access VBA)

Thu, 08 Nov 2012 13:33:49 -0700

VBA Stop Cycle

Fri, 02 Nov 2012 11:02:54 -0700

I have a macro that does everything I need it to do (find a certain word, move the contents of that cell it to another cell, and keep searching for more). However, I can't get it to stop (and move on to the next command).

Here is what I have:

' MOVE "Participant Name"
Columns("C:C").Select
Do
Cells.Find(What:="Participant", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
With ActiveCell
If .Column > 1 And .Row < Rows.Count Then
.Cut Destination:=.Offset(3, -2)
End If
End With
Loop Until IsEmpty(ActiveCell.Offset(0, 1))

I'm sure it's obvious to you gurus, but I need your help!

VBA Stop Cycle

Fri, 02 Nov 2012 11:01:04 -0700