Recent content by LW

Here is a function I wrote whe I found Excel didn't support the replace function. After each call to xlReplace, check to see if their are additional nonnumeric chrs in the string. You may also want to look into regular expressions. Hope this helps. Public Function XLreplace(ByVal Exp As String...

Take a look at the TextStream object that is in the Microsoft Scripting Runtime. (scrrun.dll) It has both a ReadLine() and WriteLine() functions. Something like: (Set a reference to Microsoft Scripting Runtime) Private Sub Form_Load() Dim fs As FileSystemObject Dim ts1 As TextStream...

With rmikesmith's help, I solved the puzzle. By moving the call to EverythingOn from Sub Workbook_BeforeClose to the statement directly preceding the Application.Quit statement of the Quit Command button it all works great. Thanks again Mike!

rmikesmith, I tried your suggestion & created a new workbook. I copied the EverythingOn and EverythingOff code to a modual. I created a command button on sheet1 with Application.Quit as the code for the button. EverythingOff is called from Workbook_Open() and EverythingOn from...

rmikesmith Here is the code that opens the workbook and calls EverythingOff: Private Sub Workbook_Open() Dim CurrentSheet As Excel.Worksheet Dim sht As Excel.Worksheet shtBackground.Visible = xlSheetVisible Application.ScreenUpdating = False 'These sheets should be...

I have an app where I use forms for user input and store that input on sheets that the user never sees. I use the following code to hide all of the menus: Public Sub EverythingOff() Application.ScreenUpdating = False Dim cb As CommandBar For Each cb In CommandBars...

acron & LSTAN I just tried your code. It works great. Thanks Guys!

acron Thanks for the quick response. I am using the following to populate the combobox and not a range: combo1.additem "" combo1.List(0,0)="Col1 Item" combo1.additem "" combo1.List(0,1)="Col2 Item" Actually I'm pulling the items from variables but I'm...

I am using Excel97. I have a 2 column combobox set to dropdown List style. How do I setup the column Headings from code?

Try this: Set a reference to the scripting runtime Option Explicit Dim fs As Scripting.FileSystemObject Dim ts As Scripting.TextStream Dim strFile As String Set fs = New Scripting.FileSystemObject strFile = "c:\MyFilename " & Format(Now, "mm-dd-yyyy") &...

There is an undocumented function erl() that will return the line number IF you use line numbers in your code. I have found a great freeware addin that will put line numbers in for you called MZTools. It does alot more. It's worth a look at www.mztools.com

Is there a reason you are using an updatebatch rather than update as you don't indicate you are using disconnected recordsets? If you are not using disconnected recordsets I would try Update. Also the MDAC 2.5 SDK says: "You should use batch updating with either a keyset or static cursor...

If your code has been working Ok, I would look at the database. Look for things that might have changed in the structure or at the data it's self. (Did someone enter something your program expect.) I would look at the key fields and the latest data in those fields. Hope this helps.

I created the following function for the same situation. Public Function ValidDate(DateStr As String, Sep As String) As Boolean 'The Function Checks a passed string for a valid date representation. Two digit years are considered invalid. 'Returns True or False Dim MM As...

Thanks Misery347, It worked like a charm!!