Posts by kwik98

kwik98 · Feb 2nd 2017

Re: Copy Row If Cell In Column Contains Todays Date

I love this code. But what if the current date isnt found. How is it handling this?

kwik98 · Jul 1st 2014

Re: email worksheets using dates

Quote from kwik98;718223

Thanks for the reply. I used your code but it doesn't seem to be checking for today's date in each column. Instead , if there is any date in the columns, it sends the email for that many times. There are always going to be dates in the columns C,D and E but they will not always be the current date. Its now sends as many emails as there are dates in these columns? Am i doing something wrong? Also, the () at the end of each TODAY seems to be referencing a Sub. I get an error with these parentheses in there. I removed them and it runs but runs as I mentioned before.

Actually, I did a little tweaking and got it to work. I changed the function to Date and it seems to be working fine now. I really appreciate the knowledge and help.

kwik98 · Jul 1st 2014

Re: email worksheets using dates

Quote from pike;718173

Hi kwik98,
you will have to loop through the columns and check against the today() function

Code

Dim rngItem
    For Each rngItem In Range("C1", Cells(Rows.Count, 3).End(xlUp))
        If rngItem.Value = TODAY() Or rngItem.Offset(0, 1).Value = TODAY() Or rngItem.Offset(0, 2).Value = TODAY() Then
            SendMail
        End If
    Next rngItem

Thanks for the reply. I used your code but it doesn't seem to be checking for today's date in each column. Instead , if there is any date in the columns, it sends the email for that many times. There are always going to be dates in the columns C,D and E but they will not always be the current date. Its now sends as many emails as there are dates in these columns? Am i doing something wrong? Also, the () at the end of each TODAY seems to be referencing a Sub. I get an error with these parentheses in there. I removed them and it runs but runs as I mentioned before.

kwik98 · Jun 30th 2014

I seem to get stuck on simple little problems in VBA ( and life..lol) I have a spreadsheet containing dates in columns C,D and E . I want to run a macro that I have ( a send email macro), if any of the dates in any of these three columns matches the current "TODAYS" date. The code in my macro works fine for the email part, but I cant get the If statements to work for me. This macro opens everyday, checks the dates in these columns and runs (emails the worksheet) if needed. If none of the cells in these rows equals the current date, the macro simply closes.
Here's the code I'm using. Everything works except If and ElseIf statements.

Code

Sub Email_ActiveSheet_As_PDF()








If Worksheets("Sheet1").Range(Columns(C)).Value = TODAY Then
 SendMail
  
ElseIf Worksheets("Sheet1").Range(Columns(D)).Value = TODAY Then
 SendMail
  
 ElseIf Worksheets("Sheet1").Range(Columns(E)).Value = TODAY Then
 SendMail
End If
End Sub

Display More

kwik98 · Jun 18th 2014

Re: zoom macro in protected mode

Quote from Luke M;716818

Actually, that's exactly what the difference between a locked and unlocked cell is. When you protect the sheet, let the user have option to select locked cells. They still won't be able to make any changes to them, and will only be able to edit the unlocked cells (which in your case, are the DV ones).

Thank you luke. Make alot of sense now. lol

kwik98 · Jun 18th 2014

Re: zoom macro in protected mode

Thank you Luke. Answers why it wasnt unzooming for me. Wish there was a way to protect all cells except for DV cells, but have it unzoom if any other cell other than DV cells were clicked, even if locked.

kwik98 · Jun 18th 2014

Re: zoom macro in protected mode

If I try to protect the "Data Entry" sheet...the zoom will not work.

kwik98 · Jun 18th 2014

Re: zoom macro in protected mode

Here is the file.

kwik98 · Jun 18th 2014

Re: zoom macro in protected mode

Quote from Luke M;716795

Welcome to the forum!
This seems to work on my machine. Was it not on yours?

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lZoom As Long
    Dim lZoomDV As Long
    Dim lDVType As Long
    lZoom = 100
    lZoomDV = 180
    lDVType = 0
    Application.EnableEvents = False
    On Error Resume Next
    lDVType = Target.Validation.Type
    On Error GoTo errHandler
    
    Me.Unprotect
    If lDVType <> 3 Then
        With ActiveWindow
            If .Zoom <> lZoom Then
                .Zoom = lZoom
            End If
        End With
    Else
        With ActiveWindow
            If .Zoom <> lZoomDV Then
                .Zoom = lZoomDV
            End If
        End With
    End If
    
exitHandler:
Me.Protect
    Application.EnableEvents = True
    Exit Sub
errHandler:
    GoTo exitHandler
     
     
End Sub

Display More

I had a short but sweet code for zooming. This one works perfectly, the way I want it to except for when I protect the worksheet. This code was my original code that I used when starting to find a solution to the small print of a DV list. I would prefer to use this one if I could, only in protected mode, it doesnt work.

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Cells.Count > 1 Then Exit Sub
  If Intersect(Target, Range("A3,B3,C3,A5,B5,C5,D5")) Is Nothing Then
    ActiveWindow.Zoom = 100
  Else
    ActiveWindow.Zoom = 140
  End If
End Sub

kwik98 · Jun 18th 2014

Re: zoom macro in protected mode

Quote from Luke M;716795

Welcome to the forum!
This seems to work on my machine. Was it not on yours?

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lZoom As Long
    Dim lZoomDV As Long
    Dim lDVType As Long
    lZoom = 100
    lZoomDV = 180
    lDVType = 0
    Application.EnableEvents = False
    On Error Resume Next
    lDVType = Target.Validation.Type
    On Error GoTo errHandler
    
    Me.Unprotect
    If lDVType <> 3 Then
        With ActiveWindow
            If .Zoom <> lZoom Then
                .Zoom = lZoom
            End If
        End With
    Else
        With ActiveWindow
            If .Zoom <> lZoomDV Then
                .Zoom = lZoomDV
            End If
        End With
    End If
    
exitHandler:
Me.Protect
    Application.EnableEvents = True
    Exit Sub
errHandler:
    GoTo exitHandler
     
     
End Sub

Display More

This is just the code I am using. Yes it works fine. My problem isnt that this code is not working, it is that it doesnt work when I protect my worksheet.

kwik98 · Jun 18th 2014

Hello, I have a worksheet with several data validation cells. I also have a macro to zoom into each DV cell when selected, to 140%, then back to 100% when any other cell other than a DV cell is selected. The code works great except in protected mode. I have tried using the standard "Protect" and "Unprotect" vba codes, and protect userinterface codes . The zoom macro ( in protected mode) zooms into the 140% but then stops after that. It will not zoom back out to 100%. Once it stops, the code will not work after that. Here is the zoom code I am using.

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim lZoom As Long
  Dim lZoomDV As Long
  Dim lDVType As Long
  lZoom = 100
  lZoomDV = 180
  lDVType = 0
  Application.EnableEvents = False
  On Error Resume Next
  lDVType = Target.Validation.Type
  
    On Error GoTo errHandler
    If lDVType <> 3 Then
      With ActiveWindow
        If .Zoom <> lZoom Then
          .Zoom = lZoom
        End If
      End With
    Else
      With ActiveWindow
        If .Zoom <> lZoomDV Then
          .Zoom = lZoomDV
        End If
      End With
    End If
 
exitHandler:
  Application.EnableEvents = True
  Exit Sub
errHandler:
  GoTo exitHandler
  
  
End Sub

Display More