Posts by Simon Lloyd

Re: data formating

For the limited explanation you gave this should work for you, it's a little crude as i didnt have time to mess around with it but should work for you[vba]Sub Duplicate_Data()
Dim Rw As Long, i As Long
Rw = Range("H" & Rows.Count).End(xlUp).Row
For i = 1 To Rw Step 1
If Range("H" & i).Value > 0 Then
Range(Cells(i, 4).Address & ":" & Cells(i, 8).Address).Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
i = i + 1
ElseIf Application.WorksheetFunction.CountA(Range("H" & i)) > 0 And Range("H" & i).Value = 0 Then
Range(Cells(i, 4).Address & ":" & Cells(i, 8).Address).Delete
i = i - 1
End If
Next i
End Sub[/vba]

Re: Test For/ Get Past Array "Subscript Out of Range" Error Message

In that case you'd need to know the error number that was given with "subscript out of range" then you can check for the error number...etc

Re: automatically send email if cell background colour changes

Cross Posted Here!

Re: VBA Macro Code To Crhange Password To Protect The VBE

Crossposted http://www.thecodecage.com/for…applications-project.html

Re: Excel macro - Downloading attachments from outlook folders

Posted all these places:
http://www.vbforums.com/showthread.php?t=630815
http://www.ozgrid.com/forum/showthread.php?t=147569&page=1
http://www.mrexcel.com/forum/showthread.php?p=2496897
http://www.vbaexpress.com/forum/showthread.php?t=34782
http://www.thecodecage.com/for…ents-outlook-folders.html
http://www.excelforum.com/exce…b-folders-of-outlook.html

Re: Excel macro - Downloading attachments from outlook folders

abhay i asked you to post ALL your crosspost links!

Re: Share a work book base on user name and password

You need to have a hidden sheet (called Sheet1 in the macro) with the usernames on and in the column next to it have the sheet name that you want them to view. You also need a sheet called Welcome as this will be the only one displayed if macros are turned off or they don't have sheet access.

This goes in the Thisworkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet
Sheets("Welcome").Visible = True
For Each sh In Sheets
If sh.Name <> "Welcome" Then
sh.Visible = xlVeryHidden
End If
Next sh
ThisWorkbook.Save
End Sub


Private Sub Workbook_Open()
    Dim Ans As Range
    Application.ScreenUpdating = False
    
    On Error GoTo Nxt
    Set Ans = Sheets("Sheet1").Columns(1).Find(What:=Environ("username"), After:=Sheets("Sheet1").Range("A1"), LookIn:=xlFormulas, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Ans <> "" Then
        Sheets(Ans.Offset(0, 1).Value).Visible = True
Sheets("Welcome").visible=False
        Application.ScreenUpdating = True
        Exit Sub
    End If
Nxt:     MsgBox "No such user Found"
    ThisWorkbook.Close False
End Sub

Re: Return tag of dynamic commandbutton as string

In a standard module add this at the top:

Public Usr As String

then in your userform:

Private Sub CommandButton1_Click()
Usr = Me.Commandbutton1.Caption
End Sub

Re: Reviewing Multiple Answer Suggestions

Micheal you seem to have posted this question in Excel Help but isn't related, you might want to contact a moderator to get them to move this thread and notify Dave Hawley of the problem

Re: Addins

I'm unsure what your problem is, do you want the code to run upon opening workbook x?, how are you triggering the code?

When running a macro in any workbook where the security is set to medium or higher you will always get that question, it's there to give the user a choice.

Have i missed the point?

Re: Append 2cells With Time

why not use =NOW() thats the time and date if not concatenate by using =A1 & " " & B1

Re: Lookup And Sum Multiple Columns

Isn't this thread related to this one? http://www.ozgrid.com/forum/showthread.php?t=95116 if so then its best you delete one or at the very least add your attachment here!

Re: Change Font Color Across Sheets

Using formula will only work in conditional formatting - without a sample workbook showing before and after results and based on what criteria we would probably be unable to help you!

ADDED BY ADMIN
Conditional Font Colors

Re: Copy Unknown Number Of Sheets To Workbook

Try:

Sheets("D").Copy After:=Workbooks("Book5").Sheets(sheets.count)

Re: Formula

All i can say is "What??????" your post doesnt make much sense, why not upload a workbook to show us what you are trying to achieve!

Look up Absolute cell referencing and Concatenation.

Re: Lookup Cell And Return A Range Of Cells

Cross posted here

Re: Format Row Based On Text Value

This is code that goes in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Rows.Count > 1 Then Exit Sub
If Target.Text = "Blank" Then
Range("D" & Target.Row & ":G" & Target.Row & ",K" & Target.Row).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    With Selection.Borders(xlDiagonalUp)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    End If
End Sub

Re: Allow Use Of Workbook On Specific Computers Only

You would do something like:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Sht As Worksheet
Sheets("Welcome").Visible = True
For Each Sht In Sheets
If Sht.Name = "Welcome" Then
Else
Sht.Visible = xlVeryHidden
End If
End Sub


Private Sub Workbook_Open()
Dim Sht As Worksheet
If Environ("computername") <> "MACHINE NAME" Then Exit Sub
For Each Sht In Sheets
Sht.Visible = True
End If
Sheets("Welcome").Visible = False
End Sub

where "MACHINE NAME" would be the actual name of your computer i.e "Warriors PC"

Re: Allow Use Of Workbook On Specific Computers Only

one other way is to make the workbook an exe file, there are programs out there that will do that, of course the easiest solution would be to have all the worksheets xlVeryHidden except a welcome sheet, that way if they dont enable macro's they only see the welcome sheet and cannot unhide the other sheets via the tool bar they can only be made visible by code!

Re: Undo Option Lost After Event Macro Code

Dave your dead right, it is a simplicity all of its own as i mentioned in post 5, in fact you could copy the sheet everytime a change is made naming the with the sheet name and time perhaps then upon close delet all the copied sheets. Bert01 great effort but as you say can be unstable so if the document is important you can't rely on it however you can rely on a direct copy of the worksheet.