Posts by max_lux

Re: Addin To Allow Mouse Wheel Scrolling In The VB6/VBA Editor

Quote from Krishnakumar

Here is another one

Nice exe, works fine, though not an Excel addin (.xla) or (.vhp)

Just to note: This exe adds a dll hook to web browser(s) so it can access the internet. I blocked it via my firewall, but things like this are highly disturbing, as a dll hook can be used for keylogging...

Hi all, long time no see. I've been working foreign and exotic places.

Here is the macro:

Sub Save_Report()
    Dim RepName as string 'Name for the saved workbook
    Repname = Sheets("Report").Range("A1").Value
    Dim WBpath As String 'Select current workbook path, so saves in same folder
    WBpath = ActiveWorkbook.Path


    Application.DisplayAlerts = False 'so overwrites if already there
    ActiveSheet.Copy 'copies current sheet


    On Error Resume Next  'if no book1, then defer to book2, etc...
    Windows("Book1").Activate '<<< ERROR here if no book1
    Windows("Book2").Activate
    Windows("Book3").Activate
    Windows("Book4").Activate
    Windows("Book5").Activate
    Windows("Book6").Activate
    Windows("Book7").Activate
    Windows("Book8").Activate
    Windows("Book9").Activate
    On Error GoTo 0


    ChDir WBpath
    RepName = Application.SaveAsFilename(RepName, _
    fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
    ActiveWorkbook.SaveAs RepName
    ActiveWorkbook.Close
End Sub

Now the problem is, in one workbook this works fine if this is the second to ninth save, but in a different workbook (the one I am currently working on), it does not resume next and defaults to an error. Is there any way to see if exists a window with the ("book#") name 1 through 9, and if so, then select it?

Thanks in advance![hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi,

I figured out the answer. See below code:

Sub Save_Report() 
    Dim RepName As String 'Name for the saved workbook
    Repname = Sheets("Report").Range("A1").Value 
    Dim WBpath As String 'Select current workbook path, so saves in same folder
    WBpath = ActiveWorkbook.Path 
     
    Application.DisplayAlerts = False 'so overwrites if already there
    ActiveSheet.Copy 'copies current sheet
     
    Dim myWb As String ' <<< Fixed with this code
    myWb = ActiveWorkbook.Name
    Windows(myWb).Activate
     
    ChDir WBpath 
    RepName = Application.SaveAsFilename(RepName, _ 
    fileFilter:="Microsoft Excel Workbook (*.xls), *.xls") 
    ActiveWorkbook.SaveAs RepName 
    ActiveWorkbook.Close 
End Sub

Thanks for browsing

Re: Remove Tabs, Seperate Data To Cells

Thanks Stan

I have data which contains numbers seperated by tabs. I would like to have each tab seperated number added to the next cell (ie a1 a2 a3 a4 a5) so each tab seperated number is in its own column.

I have included a workbook to see what kind of data I start with.

Thanks, in advance!

Re: Exit Cell Event

Many thanks

Re: Exit Cell Event

Ok thanks guys.

What I want is; if the value of the cell is zero upon exit, the adjacent option button will become value = false.

I will see if I can get this going. I obviously need some caffeine...

Thanks!

Re: Exit Cell Event

I didn't think about it because I don't know how to do it, hehe...

I am running the following code to check if a value = 0 is in specific cells. Because this code on all the cells runs each time any cell in the workbook is calculated, the screen flashes. I would like to stop this screen flashing by having the check done only when the specific cells are exited.

Thanks, in advance!

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If ActiveSheet.Name = "INPUT" Then
        Application.ScreenUpdating = False
        If Sheets("INPUT").Range("B33").Value = 0 Then
            Sheets("INPUT").obIoh.Value = False
        End If
        If Sheets("INPUT").Range("D33").Value = 0 Then
            Sheets("INPUT").obIIoh.Value = False
        End If
        If Sheets("INPUT").Range("F33").Value = 0 Then
            Sheets("INPUT").obIIIoh.Value = False
        End If
        If Sheets("INPUT").Range("I33").Value = 0 Then
            Sheets("INPUT").obIVoh.Value = False
        End If
        Application.ScreenUpdating = True
    Else
        Exit Sub
    End If
End Sub

Re: Choosing a printer using VBA

dlganswer = Application.Dialogs(xlDialogPrinterSetup).Show

Re: TextBox Spell Checker?

thanks for the reply Norie. I will have a look when I have time - currently in transit - days off - couple more...

Would there be, possibly, any way to run spell checker on a specific textbox (only) which is in a worksheet? I could always get around this by exporting the text to word, running the spellchecker, and then importing the text back to excel, but I would prefer doing this without the export.

Thanks! In advance!

Re: Changing Formula to Value

copy the column
right click
paste special
values

Re: Overwrite sheet name?

Quote

The macro saves the the names "SHEET3" but the next time it runs

I have noticed it does the same with macros I run to save files. It will work fine the first time but not the second. I haven't figured out why this is, yet. When I do I will post.

Re: Move macro

Here is another code for importing from a text file.

Public Sub OldOpen()
' Create the input strings.
Dim DataLine As String
Dim ThisCell As String


' Used for looping.
Dim Counter As Integer
Counter = 1
Dim CellCounter As Integer


' Open the file.
Open ThisWorkbook.Path + "\Temp.txt" For Input As #1


' Read the data one line at a time.
While Not EOF(1)
Line Input #1, DataLine


' Verify whether there is a tab in the text. If so,
' place each text element in a separate column.
If InStr(1, DataLine, vbTab) Then


' Set the cell counter.
CellCounter = 1


' Keep processing the line of text until finished.
While (Len(DataLine) > 0)


' Verify that the text still has tabs in it.
If InStr(1, DataLine, vbTab) Then


' Get the text to the left of the tab and place it in the cell.
ThisCell = Left(DataLine, InStr(1, DataLine, vbTab) - 1)
Sheet1.Cells(Counter, CellCounter) = ThisCell


' Make the current text the remaining tabbed text element.
DataLine = Mid(DataLine, InStr(1, DataLine, vbTab) + 1)


' Go to the next cell.
CellCounter = CellCounter + 1
Else
' The text is free of tabs. Make the cell equal to this last
' line and then clear the data line.
Sheet1.Cells(Counter, CellCounter) = DataLine
DataLine = ""
End If
Wend
Else


' Place the data in the worksheet.
Sheet1.Cells(Counter, 1) = DataLine
End If


' Update the counter.
Counter = Counter + 1
Wend


' Close the file.
Close #1
End Sub

Re: Format Phone number as it is being entered

Nice job Dave, I've wondered about code that does such a thing.

Here it is for with the leading country number ie for North America +1 (000) 867-5309

Would have to be formatted for Europe differently

Max Length (17)

Private Sub TextBox1_Change()
    Dim Phone As String
    Dim Length As Integer
    Phone = TextBox1.Text
    Length = Len(Phone)
    Select Case Length
         
    Case 1
        TextBox1.Text = Format(TextBox1, "0 (")
    Case 3
        TextBox1.Text = Format(TextBox1, "0(0")
    Case 6
        TextBox1.Text = Format(TextBox1, "(000") & ") "
    Case 11
        TextBox1.Text = Format(TextBox1, "(000)000") & "-"
    End Select
End Sub

Now to add isNumeric code, and figure out how to allow the user to backspace and correct without having to select everything

Re: Permanent Custom Menu

.BeginGroup = True

...doesn't work.

I asked this before and never got an answer, so I assume it can't be done.

Re: Protect / Unprotect Worksheets

Even if the xlUnlocked cells is done, it is undone when you close the workbook (one of Excels wonderful quirks).

You must run that code in the Workbook_Open event, in the workbook object. Then that is done when the workbook is opened.

Re: Event For Protection

Quote from Will Riley

One thing you can do is to make sheets you really don't want people to see VeryHidden and password protect the VB module

Done and done. I was just wondering if there was any way to implement sheet protection if it was haxrd. I could add protect code for worksheet_change, worksheet_activate, which would not make it perfect, just a pain in the a** for anyone who should try to mess with sheets.

I know there's a way to get into everything. I have tryed hack one of my own workbooks to find out how secure it is. So far I have been unable to access the VBA project, using downloaded VB hack software, but I'm sure there are those that could. If I can't do it, however, I'm not too worried about my users.

***Disclaimer: I only use hack software on my own work to ensure security. I would not think about using it to hack anyone else's work. That would be unethical and immoral.

Re: Event For Protection

Thanks Norie. I didn't expect so, but was wondering if any gurus had a trick up their sleeve.

Would it be possible to write an event whereby if a sheet became unprotected for (00:00:01), that it would be automatically protected? This would severly limit a sheet haxrs ability to do anything, even if unprotecting a sheet, yet would allow code to be run which required sheet unprotection.