Posts by rollis13

Quote from rollis13

Move the line:

Dim lTarget As Range

before the line (on top):

Private Sub Worksheet_Change(ByVal Target As Range)

Doesn't this make some sense ?

Instead, do I need to say: Cut the line of code Dim lTarget As Range and Copy it as first line in the sheet's vbe module.

Move the line:

Dim lTarget As Range

before the line (on top):

Private Sub Worksheet_Change(ByVal Target As Range)

Well, now I'm confused because I can still confirm that your macro in post #1 works for me on Excel 2016. I must say that my Office is a 32-bit version and not the 64-bit installed on your PC.

Sorry, but you didn't state in your profile that you were using 365. As you can see I'm using 2016 so ... I tried a quick search on the web but didn't find a solution.

Seems to me that you already have given reference to the right Libraries. My only suggestion/guess:

try moving the line Set oDoc = .GetInspector.WordEditor just before the line .Display.

I tested your macro and I must say that it does not generate any problem. Exactly, which error appears to you and in which line the macro stops (highlighted).

I gave you a hint for cell E42, how about giving F42 a try by yourself .

This could be a solution for cell E42 (then pull down):

=IF(C42="","",IF(OR(C42=18,C42=30),730,745))

I'll just point out that your 07:00 in column H are not the precise 7 o'clock since all have a few seconds. That's why you won't find a response in column O.

Hi to all.

Maybe it's only a typo: the folder is System32

Quote

I would like the VBA code to Hide/Unhide a Worksheet in a Workbook with a Formula.

But do you need VBA or do you need a Formula ? By the way, can't be done by formula.

You could use this macro to be pasted in the "Content" tab's module. It will Hide/Unhide the tab "Data" every time you click cell C6 as you asked.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim pwd As String
    If Not Intersect(Target, Range("C6")) Is Nothing Then
        If Sheets("Data").Visible = True Then
            Sheets("Data").Visible = False
        Else
            pwd = InputBox("To unhide please provide a valid password")
            If pwd <> "test" Then GoTo NoPwd
            Sheets("Data").Visible = True
        End If
NoPwd:
        Application.EnableEvents = False
        Target.Offset(1, 0).Select
        Application.EnableEvents = True
    End If
End Sub

As long as your empty cells don't contain formulas, have a try with this macro on your active sheet. Change last row detection method if your real sheet has other areas compiled.

Option Explicit
Sub DeleteBlankRowsInAreas()
    Dim x      As Long
    Dim lr     As Long
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    lr = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    For x = lr To 4 Step -1
        If Application.CountBlank(Range("A" & x & ":C" & x)) = 3 Then Range("A" & x & ":C" & x).Delete Shift:=xlUp
        If Application.CountBlank(Range("E" & x & ":G" & x)) = 3 Then Range("E" & x & ":G" & x).Delete Shift:=xlUp
        If Application.CountBlank(Range("I" & x & ":K" & x)) = 3 Then Range("I" & x & ":K" & x).Delete Shift:=xlUp
    Next
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

I think you only need to change the filter line to:

raw.Range("A4").AutoFilter Field:=5, Criteria1:=region

because Field:=5 is where to look for Criteria1:=region.

Yes, ok. From the beginning I wondered what it was doing there but since I've never considered what the macro really did ... .

Thanks for the positive feedback, glad having been of some help.

Well, it was a problem with 64bit versions and a couple of years ago I found a workaround ... that's why I went back to a 32bit version.

Maybe if I look throught my snippets it could come up but since you say you "found an extra line of code not needed" I would be very happy to know which one .

Ps. I think it was to add a Sendkeys "{NUMLOCK}", True before the end of the macro or just after the last offending SendKey.

You got Sheet1 module wrong. I said don't use Worksheet_Change, instead use Worksheet_Calculate as indicate at the end of my post. When D3 or D5 change it will call your Make_Board macro.

Try this, to trigger your macro instead of event Worksheet_Change use the event Worksheet_Calculate, like this:

In the ThisWorkbook module paste:

Private Sub Workbook_Open()
    With Sheet1     '<- adjust sheet name as needed
        Prev1 = .Range("D3").Value
        Prev2 = .Range("D5").Value
    End With
End Sub

In a Standard module paste:

Public Prev1   As Variant
Public Prev2   As Variant

In the Sheet's module paste:

Private Sub Worksheet_Calculate()
    If Range("D3").Value <> Prev1 Or Range("D5").Value <> Prev2 Then Make_Board
End Sub

Save and Restart workbook to activate the public variables before use.

Thanks for the positive feedback, glad having been of some help.

Add these lines of code to your macro, could be a simple solution. It will add a random number at the end of Filename:

'...
    Sheets(sheetArray).Select
    '---------- added ---------
check:
    If Dir(strPathLocation & Filename & ".pdf") <> "" Then
        Filename = Filename & "_" & Int((10 * Rnd))
        GoTo check
    End If
    '---------------------------
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=strPathLocation & Filename
End Sub

To save the file in a specific folder (B13=custname) change the line that saves it to:

.SaveAs Filename:=path & custname & "\" & fname, FileFormat:=52

Ps. the custname folder must already exist.

That's what I said, you just have to adapt my suggestion to your data (cell reference).