Posts by stildawn

Re: More Efficient Way To Code This - Hiding Rows Based On User Input

Do you have a quick example of what you mean? Trying to figure it out as it sounds promising.

Re: More Efficient Way To Code This - Hiding Rows Based On User Input

Would that increase performance considerably?

I was thinking that its doing the code every time the cursor moves, maybe there is a way or a replacement to "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" that stops running the code all the time, once the fields checked (e.g. E16) have been moved past or something?

Hi All

I have made up a form in Excel (not a userform) it has a bunch of drop down boxes etc for users to make certain selections, based on these selections more or less information is required from the user.

I have this code below, which captures the users selections and expands or subtracts the sheet (hiding rows) to generate what they need.

Its getting a bit laggy, so I ask how would you awesome vba gurus make this code more efficient and less laggy:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ThisWorkbook.Sheets("Sales Request Form")
    '.Unprotect
    If .Range("E16") = "Existing Account" Then
        .Rows("17:24").Hidden = False
    Else
        If .Range("E16") = "New Account" Then
            .Rows("17:24").Hidden = True
            .Rows("28:117").Hidden = False
            .Rows("122").Hidden = False
            .Rows("127").Hidden = False
            .Rows("132").Hidden = False
            .Rows("137").Hidden = False
            .Rows("142").Hidden = False
            .Rows("147").Hidden = False
            .Rows("152").Hidden = False
            ThisWorkbook.Sheets("Implementation Team").Rows("36:40").Hidden = False
        Else
            If .Range("E16") = "RFQ Account" Then
                .Rows("17:24").Hidden = True
                .Rows("28:117").Hidden = False
                .Rows("122").Hidden = False
                .Rows("127").Hidden = False
                .Rows("132").Hidden = False
                .Rows("137").Hidden = False
                .Rows("142").Hidden = False
                .Rows("147").Hidden = False
                .Rows("152").Hidden = False
                ThisWorkbook.Sheets("Implementation Team").Rows("36:40").Hidden = False
            Else
            End If
        End If
    End If
    
    If .Range("H11") = "X" Then
        ThisWorkbook.Sheets("Implementation Team").Rows("41:47").Hidden = False
    Else
    End If
    
    If .Range("H10") = "X" Then
        ThisWorkbook.Sheets("Implementation Team").Rows("48:55").Hidden = False
    Else
    End If
    
    If .Range("H13") = "X" Then
        ThisWorkbook.Sheets("Implementation Team").Rows("56:62").Hidden = False
    Else
    End If
    
    If .Range("H12") = "X" Then
        ThisWorkbook.Sheets("Implementation Team").Rows("63:70").Hidden = False
    Else
    End If
    
    If .Range("H14") = "X" Then
        ThisWorkbook.Sheets("Implementation Team").Rows("71:88").Hidden = False
    Else
    End If
    
    If .Range("E18") = "X" Then
        .Rows("28:44").Hidden = False
        .Rows("76:86").Hidden = False
        .Rows("109:117").Hidden = False
        .Rows("122").Hidden = False
        .Rows("127").Hidden = False
        .Rows("132").Hidden = False
        .Rows("137").Hidden = False
        .Rows("142").Hidden = False
        .Rows("147").Hidden = False
        .Rows("152").Hidden = False
    Else
    End If
    
    If .Range("E19") = "X" Then
        .Rows("38:44").Hidden = False
        .Rows("116:117").Hidden = False
        .Rows("122").Hidden = False
        .Rows("127").Hidden = False
        .Rows("132").Hidden = False
        .Rows("137").Hidden = False
        .Rows("142").Hidden = False
        .Rows("147").Hidden = False
        .Rows("152").Hidden = False
    Else
    End If
    
    If .Range("E20") = "X" Then
        .Rows("38:42").Hidden = False
        .Rows("116:117").Hidden = False
        .Rows("122").Hidden = False
        .Rows("127").Hidden = False
        .Rows("132").Hidden = False
        .Rows("137").Hidden = False
        .Rows("142").Hidden = False
        .Rows("147").Hidden = False
        .Rows("152").Hidden = False
    Else
    End If
    If .Range("E21") = "X" Then
        .Rows("28:44").Hidden = False
        .Rows("87:108").Hidden = False
        .Rows("116:117").Hidden = False
        .Rows("122").Hidden = False
        .Rows("127").Hidden = False
        .Rows("132").Hidden = False
        .Rows("137").Hidden = False
        .Rows("142").Hidden = False
        .Rows("147").Hidden = False
        .Rows("152").Hidden = False
    Else
    End If
    
    If .Range("E22") = "X" Then
        .Rows("37:75").Hidden = False
    Else
    End If
    
    If .Range("E23") = "X" Then
        .Rows("62:75").Hidden = False
        .Rows("116:117").Hidden = False
        .Rows("122").Hidden = False
        .Rows("127").Hidden = False
        .Rows("132").Hidden = False
        .Rows("137").Hidden = False
        .Rows("142").Hidden = False
        .Rows("147").Hidden = False
        .Rows("152").Hidden = False
    Else
    End If
    
    If .Range("E24") = "X" Then
        .Rows("45:61").Hidden = False
        .Rows("109:115").Hidden = False
    Else
    End If
    
    If Application.WorksheetFunction.CountA(.Range("E110:E114")) > 0 Then
        ThisWorkbook.Sheets("Implementation Team").Rows("89:100").Hidden = False
    Else
    End If
    If Application.WorksheetFunction.CountA(.Range("C118:C121")) > 0 Then
        ThisWorkbook.Sheets("Implementation Team").Rows("101:105").Hidden = False
    Else
    End If
    
    If Application.WorksheetFunction.CountA(.Range("C123:C126")) > 0 Then
        ThisWorkbook.Sheets("Implementation Team").Rows("106:109").Hidden = False
    Else
    End If
    
    '.Protect
End With
    
End Sub

Re: combine two codes

Yeah the if is redundant in this case cause the sheet will never be HSK as your looking for the current date haha. Guessing you got the code elsewhere on the net? Its easy to get code but not the context if you know what I mean.

Happy to help

Re: combine two codes

Sorry it should be Sheets(wsName) with a extra s lol.

Re: Ability to schedule opening and running, but also local opening and editing?

Yeah that was my first thought, but since the task is also run on her machine, the username will be the same... wont it?

Re: combine two codes

I was meaning in the open procedure something like:

Private Sub Workbook_Open()
    Dim wsName As String 
     wsName = Format(Date, "dd-mm-yyyy") 
     Worksheets("HSK").Move before:=Sheet(wsName)
     Worksheets(wsName).Activate 
End Sub

Since the name will never be "HSK" since you are looking for a date named sheet, you don't need the IF Not block

Re: combine two codes

From a quick look, just put code 2 after code one (within the same procedure), and rename sh to Sheets(wsName)

Re: Ability to schedule opening and running, but also local opening and editing?

Probably not reliably to be honest, you know how normal users are. I'm thinking is there a way for the task scheduler to open a separate excel file with some other code it in activating the procedure in the main one?

Hi All

I have a register of customers with an expiry date column, basically I have designed code that will check the dates and send emails, records the date this happens and saves, this is working fine.

The issue I have is that this excel file needs to be opened using windows Task Scheduler each day to process and create/send these emails, however at the same time the user herself would occasionally need to open the file (without running my VBA code) and add new customers, amend data etc and save.

So I need an idea on how to code for this in the Workbook_Open() sub. So that the file can be opened by task scheduler and automatically run the vba code without any user imput at all. But at the same time the user can open the file without the code running.

I had initially thought of a Activeworkbook.readonly = true check using a VBS script for the task scheduler to open the file as read only, while the user would just open it normally.

However both the user and the code needs to save the workbook, so I cant use this readonly method.

Is there a way for excel to know "how" the file was opened? Like via shortcut or something maybe?

Thanks in advance

Re: Add together values with a duplicate key identifier

Most excellent, that would work perfectly I believe and is efficient as.

Thanks a bunch

Re: Add together values with a duplicate key identifier

Its a pretty massive process in total though, the code is already slow enough doing the other things that need to be done to be honest lol. If it gets any slower I'm going to have to code in a progress bar so the user doesn't think its crashed haha.

Hi all

I have a massive list of data. Which is invoices generated for jobs.

In column A, is a number that is unique per "job" a few columns over is a few value columns for costs.

This is generated automatically by a separate system. What I need to do is have some VBA code to tally all the various cost figures (all separate invoices) for each "job" into a total and delete the other invoices.

Here is a real quick example:

[TABLE="class: grid, width: 500"]

A

B

C

1

Job Number

Cost of Freight

Cost of Services

2

6BH555

60

60

3

7BH666

70

70

4

6BH555

60

60

5

8BH777

80

80

6

7BH666

70

70

7

8BH777

80

80

[/TABLE]

So here we have three unique job numbers, which have 2 invoices each (they could have many invoices, or there could only be one in the real data)

I need code that would turn this into:

[TABLE="class: grid, width: 500"]

A

B

C

1

Job Number

Cost of Freight

Cost of Service

2

6BH555

120

120

3

7BH666

140

140

4

8BH777

160

160

[/TABLE]

As you can see the invoices are tallied together to get a total for the job numbers.

This is what I need but on a mass scale, and of course all the cost values etc are different, there could by many invoices per job, or there could just be one line in which case nothing would need to be tallied.

I know I could code this myself, but it would be a very long winded way as I am coming up blank with efficient ideas on how I would do this.

So any ideas on how to do this efficiently.

Cheers

Re: Writing a Before_Close vba code into a newly created and saved excel file?

Awesome thanks, I'll see if I can figure this out, I'm sure I can.

Cheers

Hi All

So I have raw reports, which users then run through a Excel tool I have created. The tool does a bunch of stuff to the raw report and then does the following:

With Thisworkbook.Sheets(1)
    '#### Saves as clean file ####
    IniName = "Siemens Exception Report - " & Format(Now, "dd-mm-yy")
    
    savepath = Application.GetSaveAsFilename(InitialFileName:=IniName, FileFilter:="Excel (*.xlsm),FilterIndex:=*.xlsm")




    Dim NewWb1 As Workbook
    Set NewWb1 = Workbooks.Add




    .Cells.Copy NewWb1.Sheets(1).Range("A1")




    NewWb1.SaveAs savepath,52




    NewWb1.Close
End With

As you can see this saves a clean excel file, I do this to exclude all of the VBA code from the tool so that the newly created report is clean and doesn't prompt the user for anything.

With this clean report, the user then goes in and adds come data into some cells, based on what the tool has done and their investigation into the reason etc.

Eventually a monthly report is run, which opens all these daily reports (with in a date range) and does some KPI stuff. This is all good.

My issue is, that the monthly report needs the "dd-mm-yy" in the file and for the daily files to be in one folder in order to grab the right ones. But since the users need to be able to open these daily reports and make changes, I don't think they can be trusted to keep the file naming and location consistent.

So I'm thinking, is there away in the code above, to add a "before_close" vba command in the newly created daily report, that simply always saves a copy (and overwrites) correctly into a specific folder, without the user knowing its going on, so the user can rename the file and location as many times as they want, but each time the close it, its still saved correctly elsewhere (with the updates they have done etc).

I think if this code was just saved into the VBA or the original "Siemens Exception Report - dd-mm-yy.xlsm" then it should work behind the scenes forever right?

Private Sub Workbook_BeforeClose(Cancel As Boolean)




With ThisWorkbook.Sheets(1)
    CreatedDate = ThisWorkbook.Sheets("HiddenDateSheet").Range("A1").Value
    Filename = "C:\Specific Folder\Siemens Exception Report - " & Format(CreatedDate, "dd-mm-yy") & ".xlsm"
    
    ThisWorkbook.SaveAs Filename, 52, , , , , , 2
End With




End Sub

Obviously I would be creating the "CreatedDate" range value in the original saving so that it stays static no matter what day the user works on the file.

But how do I get just this code into the new clear file.

Thanks

Re: GetSaveAsFileName is not accepting InitialFileName if from variable?

Well that worked haha, I'm sure I have used full stops before though.

Looks ugly as sin though, might try some other options.

Thanks

Hi All

I have this code:

IniName = "Siemens Exception Report - " & Format(Now, "dd.mm.yy")
    
    savepath = Application.GetSaveAsFilename(InitialFileName:=IniName, FileFilter:="Excel (*.xlsx),FilterIndex:=*.xlsx")

However when I step through the code and the saveas dialog comes up, the initial file name is always blank instead of having "Siemens Exception Report - 24.07.14" like its ment to.

I need the dialog box cause the users might want to name it something else, but I need the IniName as a suggestion/in case they don't rename it.

Re: Runtime Error 16 - Expression too complex

It is very odd, checking that valid data was available was one of the first things I checked, but it is.

After mucking around and googling I have solved it kind of.

By setting the dates into a variable first it solves it so:

Diff1 = CDate(.Range("U" & CurRow)) - CDate(.Range("T" & CurRow)) 'Received the error here
Diff2 = CDate(.Range("V" & CurRow)) - CDate(.Range("U" & CurRow)) 'Stepping to here it also errors
Diff3 = CDate(.Range("W" & CurRow)) - CDate(.Range("V" & CurRow)) 'And here

Becomes:

D1 = .Range("U" & CurRow)
D2 = .Range("T" & CurRow)
Diff1 = CDate(D1) - CDate(D2) 'Received the error here
D1 = .Range("V" & CurRow)
D2 = .Range("U" & CurRow)
Diff2 = CDate(D1) - CDate(D2) 'Stepping to here it also errors
D1 = .Range("W" & CurRow)
D2 = .Range("V" & CurRow)
Diff3 = CDate(D1) - CDate(D2) 'And here

And for some unknown reason this solves the problem.

Re: Runtime Error 16 - Expression too complex

CurRow is a global variable which is populated by a loop that is happening in some Userform code.

In the test runs, CurRow is working correctly.

The date value is a date and time value in the cells like "02/07/14 10:00" etc

Re: How to declare this Array usage?

Haha all good

Your second bit about the variant worked perfectly so cheers