Posts by bazjack

Re: Prevent sheet deletion

Hi, Mike. If you're looking to loop through all sheets and perform the same operation on each, you can use the For Each construct:

Public Sub test()


    Dim wb As Workbook, ws As Worksheet
    
    Set wb = ThisWorkbook
    
    For Each ws In wb.Worksheets
        'run code
        MsgBox ws.Name
    Next ws
    
    Set ws = Nothing
    Set wb = Nothing


End Sub

For example, this procedure will display the name of each worksheet, one after another, till all have been displayed.

If the operation differs for each sheet, you can add a Select Case statement inside the loop that picks the correct operation.

If this works for you, you won't need to have the End sheet. However, if you do want to use a hidden worksheet in code, you can set that worksheet's Visible property to xlSheetVisible before beginning to use it, and then to xlSheetVeryHidden once the code using it is finished. This will make it accessible to you without the user ever knowing it's there.

Hope this helps.

Amanda

Re: Joining tables using common field.

Hi, Suzanne. Do you have Microsoft Access?

This is the kind of thing that is better done in Access than Excel. I took the two worksheets with data from your posted Excel file and turned them into two Excel tables, then wrote the query that you need. You can paste data from Excel right into these tables, and from the tables or query back into Excel. The query is called JoinedTables; just double-click it to open.

I know this isn't quite what you're looking for, but I hope that it helps you. If you frequently need to do this kind of data management, learning the basics of Access is well worth the time and effort.

If you don't have Access, or are still looking for an Excel solution, posting the non-working Excel formula would be helpful.

Amanda

Re: Launching VBA code from outside of Excel

It doesn't look like you've initialized the Excel.Application object xL. Try adding the line Set xL=myXL.Application before the line with the error.

This code will resize the Excel window to make sure that it doesn't go behind a standard-sized Windows taskbar. It only triggers if the window is not maximized, because if it is, the taskbar shouldn't be covering it anyway.

I know this isn't exactly what you're looking for, but I hope it may help you anyway.

bazjack

Private Const LOGPIXELSY = 88 ' Logical pixels/inch in Y
Private Const VERTRES = 10 ' Vertical width in pixels
Private Const TWIPSPERINCH = 1440


Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDc As Long, ByVal nIndex As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hDc As Long) As Long


Public Function GetScreenHeight() As Integer


    Dim hDc As Long, lngRetVal As Long
    Dim varScreenY As Variant
    Dim varPixToInchY As Variant


    hDc = GetDC(0)


    'Get the Screen size in pixels for Y
    varScreenY = GetDeviceCaps(hDc, VERTRES)


    'Get the conversion of pixels/inch for Y
    varPixToInchY = GetDeviceCaps(hDc, LOGPIXELSY)


    lngRetVal = ReleaseDC(0, hDc)


    'Convert to Points.
    GetScreenHeight = (varScreenY / varPixToInchY) * 72


End Function


Public Sub FixScreen()


    Dim RealScreenHeight As Integer
    
    RealScreenHeight = GetScreenHeight - 20 'if the taskbar is 20 points high, which is standard.


    With Application
        If .WindowState = xlNormal Then
            If (.Top + .Height) > RealScreenHeight Then .Height = (RealScreenHeight - .Top)
        End If
    End With


End Sub

I have also grouped controls which couldn't be placed in frames by creating and populating Collections when the form was opened. For example, in the Form_Open event, I'd create the Collection object colMileageControls and populate it with all controls that deal with mileage information. Then I wrote functions which hid and unhid entire collections, and used those to manage the controls on the form.

The relevant topic in Excel Help is the Collection object.

I don't know if this was the best way to do it, but it worked.

Hope this helps

bazjack

You can refer to the contents of a cell in code like this:

Workbooks("master").Worksheets("Sheet1").Range("B2").Value

You may want to name each input cell on the worksheet; then you can refer to it using the range name. For example, if you name cell B2 FileName:

Workbooks("master").Worksheets("Sheet1").Range("FileName").Value

This will make your code more descriptive, and it will be easier to change the format of the worksheets if necessary.

Hope this helps

bazjack

Hi, Kevin. Check out http://www.devguru.com/Technol…ref/filesystemobject.html . If you are doing a lot of work with file and directory management, it's worth the time to learn this set of objects. That site has code snippets and excellent explanations.

One cautionary note: the MoveFolder function will not work when moving folders from one drive to another. You can get around this by copying the folder to the target, then deleting it from the source.

Good luck!

bazjack

Two more suggestions:

Within the CreateTextFile statement, make sure you use w.Name instead of ActiveSheet.name.

Try using w.Cells(i,1).Value instead of w.Cells(i,1).

Hi, chappie. It looks like you need to move the CreateTextFile line down to be the first statement in the For Each loop, and move the Close line up to be the last statement within that loop. You can use the same variable a for each file, but you will need to call CreateTextFile once for each different file you want to create.

Also, I'm not sure what you mean by repeated data, but you may need to use w.Cells inside the loop.

Hope this helps

bazjack

With A2 being the cell which lists years of service:

=IF(A2>=25,30,IF(A2>=17,25,IF(A2>=10,20,IF(A2>=1,15,0))))

Hope this helps

bazjack

You can use the formula after the Debug.Print to return the number of that first empty row. I just put in the Debug.Print to make it an actual statement, since everything to the right of it represents a value. It seemed like you've got empty rows from 2 to 10000 because you don't initially know how long your existing file is and so you're adding data beginning with 10000. If that's true, you can add data starting with row (ActiveSheet.Range("A65536").End(xlUp).Row + 1) and not end up with those blank rows.

Hope this helps

bazjack

The attached workbook will display only one sheet when it's opened from the wrong location. That sheet would be used only to notify the user that they should be opening the shared workbook.

If the workbook is opened from the correct location, it will display all worksheets. The user can interact as normal with it. When it's closed, it will again hide all sheets and save itself in that state.

If you decide to use a prevent-execution strategy instead of a prevent-copy strategy, this is a place you could start.

Hope this helps

bazjack

I don't know of a way to do what you're asking, but I have a possible workaround for you.

If the workbook needs to always be used from a certain location that will never (or very rarely) change, you can put code in the Workbook_Open procedure of the ThisWorkbook object that checks whether the workbook's in the correct location. If it isn't, you could do something like display a message box saying "Please use this only from its shared location", then close the workbook automatically.

This wouldn't stop the users from copying the workbook to their C drives, but it certainly would stop them from using it from their C drives.

I have used this method to ensure that a user cannot run the file from a certain prohibited location; it should work as well for making sure it's only run from a certain permitted location.

Hope this helps

bazjack

[Note: I just realized this can be bypassed in Excel when the users open the workbook holding down the Shift key. I'm working on an alternate suggestion to that right now.]

The line

Debug.Print ActiveSheet.Range("A65536").End(xlUp).Row + 1

will print the first row in which column A has no data, looking upward from the bottom of the worksheet. It will work unless all cells in column A have data, in which case it returns the value 2.

Hope this helps.

bazjack

Hi, Chris. Check out the PrintOut method as it applies to the Worksheet object.

Hope this helps

bazjack

Hi, James. Several times you select cells, then call a method on Selection; if you remove those selects and call the methods directly from the objects, without selecting them first, that should give some benefit. I don't know how significant it will be, but every little bit adds up, especially if the procedures are called many times.

Hope this helps

bazjack

Hi, Chris. Here is a function that I have used to get the temp directory, which sounds like it could work for what you are doing.

'===================================================
'Function: FindTempDirectory
'Description: Displays a message box giving the Temp directory.
'Where to place code: Module


'Author: Karl Moore
'http://www.vb-world.net/articles/winapi2/index4.html
'==================================================


Public Declare Function GetTempPath Lib "kernel32" _
Alias "GetTempPathA" _
(ByVal nSize As Long, ByVal lpBuffer As String) As Long


Public Sub FindTempDirectory()
    
    Dim strTmpDir As String
    Dim lngSize As Long
    
    strTmpDir = Space(255)
    lngSize = 255
    
    Call GetTempPath(lngSize - 1, strTmpDir)
    
    strTmpDir = TrimNull(strTmpDir)
    
    MsgBox strTmpDir


End Sub


Public Function TrimNull(MyString As String)


    Dim intPosition As Integer
   
    intPosition = InStr(MyString, Chr$(0))
    If intPosition Then
          TrimNull = Left(MyString, intPosition - 1)
    Else: TrimNull = MyString
    End If
  
End Function

Hope this helps

bazjack

Check out Conditional Formatting under the Format menu; for the example you mentioned, you will want to change the first combo box in the dialog from "Cell Value Is" to "Formula Is", then enter the formula that needs to be true. In your example, you would highlight A1:A19, select Format - Conditional Formatting, select "Formula Is", and enter =($A$20="Y").

Hope this helps

Amanda

Hi, Peter. You could use a public byte variable HelperResult. When the Helper form gets opened, set HelperResult to zero; when it gets closed, set it to vbYes if closed with the Yes button, vbNo otherwise. Then you can read HelperResult from the Interface form.

I don't know if this is the best way to do this, but I think it should work.

Hope this helps

bazjack

Try adding a single quote to the beginning of each date-like value as you output it. This should force Excel to treat the value as a string. The single quote will appear in the formula bar, but when you access the value of the cell using (for example) ActiveCell.Value, it will be omitted.