Solved

Excel code stops

Posted on 2014-02-20
11
369 Views
Last Modified: 2014-02-24
I am using the attached file but have issues getting a button to work as it pauses in the middle of the code.

I want to automate the add user process. Each use requires 4 rows so it has to go to the first row of the current user and copy the 4 rows then paste them underneath. I want it to the set some values also, but it's not getting that far....

The area of code thats causing the issue is

ActiveCell.Offset(4, 0).Rows("1:1").EntireRow.Select
    Selection.Insert Shift:=xlDown
    Application.SendKeys "~", True
    Application.CutCopyMode = False
    ActiveCell.Range("A1:F1").Select

Open in new window


I'm using
    Application.SendKeys "~", True
or  Application.CutCopyMode = False

to get rid of the selection area but no joy.
I did try to use a copy to code but no joy either

Anyone got any ideas?

Thanks in advance.
ppe.xlsm
0
Comment
Question by:Majicthise
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39875733
When you say get rid of the selection area, do you mean that you don't want to cancel the range selection, or do you mean by deleting everything?

If you mean cancel the range selection, you can pretty much ignore it. if it really bothers you, you can simple do something like
Range("A1").select

Open in new window

, and it will select another cell.

If you mean to delete all the info inside, you can do
Selection.Delete

Open in new window


To help you understanding what the 2 lines mean,
    Application.SendKeys "~", True
   Application.CutCopyMode = False

Application.SendKeys "~", True means pretending you are physically pressing the ~ key on the keyboard. I really don't know what good will it do in your situation.

Application.CutCopyMode = False means to cancel the Copy action. To make it easier to understand, when you select a range and press Ctrl-C to copy, you will see this dotted like around the selected range stating that the select data is on the clipboard.
When you press ESC, the dotted line will disappear. Application.CutCopyMode = False is basically same as pressing the ESC key to cancel the copy mode.

So none will do what you want. To move to another selection area, simply do Range("??").Select
0
 

Author Comment

by:Majicthise
ID: 39876092
The sendkeys ~ is supposed to act like pressing enter on the normal (non numerical) keypad

During testing (outside of the code), that cancels the selection area - the moving dots selection iykwim.

It doesn't work in the code = nor does sending escape key

The cutcopymode=false doesnt work either

I also tried to select another cell and that doesnt work.

The code just stops after pasting the rows.  know thats here it stops cos I've add a msgbox after the step and it's never displayed. The problem is the code stopping - there are more steps after this to perform - they're just dont happen.

Check the code in the form behind the ok button....

Basically i want the equivalent of pressing esc after a copy & Paste
and then the code to resume.

Hope this clears it up a bit?
Thanks
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39876497
Not sure why

Application.CutCopyMode = False

isn't working. Thats exactly what you want to do, cancel the Copy mode.

How about:

SendKeys ("{ENTER}")

Thanks
Rob H
0
 

Author Comment

by:Majicthise
ID: 39876661
Nope. Tried that too.
Tilde & enter make no diference
Page up/down
Nothing

The code stops witht range highlighted.
I can press esc or enter on the keyboard and it does get rid of the selection
But not in the code....
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39876700
If I interpret your script above correctly:

prior to copied section a range is copied then

ActiveCell.Offset(4, 0).Rows("1:1").EntireRow.Select -- Move down 4 rows and insert a row

Selection.Insert Shift:=xlDown -- Pushes row down to allow for insert, as you are inserting a whole row you probably don't need this

Application.SendKeys "~", True -- Press Enter, this will paste the contents of the clipboard into new row.

Application.CutCopyMode = False -- cancel copy mode, normally if you press enter in Copy mode it will cancel Copy mode anyway, as opposed to pressing Ctrl + V to be able to keep copy mode for another paste, so not required.

ActiveCell.Range("A1:F1").Select -- Select cells A1:F1

Alternative for
Application.SendKeys "~", True

ActiveSheet.Paste

This will paste the contents of clipboard and will cancel copy mode.

Thanks
Rob H
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 35

Accepted Solution

by:
mvidas earned 500 total points
ID: 39876876
The code, it seems, is stopping after the .Insert method is called. This happens no matter how it is called, even if the button code is only "activecell.entirerow.insert"

Your use of user-defined functions in your conditional formatting is causing this issue.

You can simplify your conditional formats to accomplish the same thing without using UDFs. Make the following changes:

Delete all but one of your "isbad" formats (just to keep existing formatting). For the remaining one:
Change "Applies to" to   =$J$5:$W$872
Change formula in rule to   =$J5<$I5

Delete all but one of your "toomuch" formats. For the remaining one:
Change "Applies to" to   =$J$5:$W$872
Change formula in rule to   =($J5-$I5)/7<INDIRECT(SUBSTITUTE($G5,"/","_"))

Delete all but one of your "isformula" formats. For the remaining one:
Change "Applies to" to   =$A$5:$F$872
Change formula in rule to   =MOD(ROW()-1,4)

Your conditional format for the Yes/No in the active column is fine.

As for your button code, you can clean that up using thsi instead:
Private Sub butnok_Click()
 Dim r As Long
 If Worksheets("PPE").FilterMode = True Then Worksheets("PPE").ShowAllData
 r = ActiveCell.Row - (ActiveCell.Row - 1) Mod 4
 Rows(r).Resize(4).Copy
 Rows(r + 4).Resize(4).Insert
 Range("CA" & r + 4 & ":CB" & r + 7).Delete Shift:=xlUp
 Application.CutCopyMode = False
 Range("A" & r + 4).Resize(1, 6).Value = Array(tbclock, "Yes", UCase(tbsname), tbfname, cbotrade, cboarea)
 Unload Me
End Sub

Open in new window


I also added something to fix your columns CA and CB when rows are inserted there so it doesn't mess up your Trade/Work Area boxes.

Matt
0
 
LVL 35

Expert Comment

by:mvidas
ID: 39876877
The code, it seems, is stopping after the .Insert method is called. This happens no matter how it is called, even if the button code is only "activecell.entirerow.insert"

Your use of user-defined functions in your conditional formatting is causing this issue.

You can simplify your conditional formats to accomplish the same thing without using UDFs. Make the following changes:

Delete all but one of your "isbad" formats (just to keep existing formatting). For the remaining one:
Change "Applies to" to   =$J$5:$W$872
Change formula in rule to   =$J5<$I5

Delete all but one of your "toomuch" formats. For the remaining one:
Change "Applies to" to   =$J$5:$W$872
Change formula in rule to   =($J5-$I5)/7<INDIRECT(SUBSTITUTE($G5,"/","_"))

Delete all but one of your "isformula" formats. For the remaining one:
Change "Applies to" to   =$A$5:$F$872
Change formula in rule to   =MOD(ROW()-1,4)

Your conditional format for the Yes/No in the active column is fine.

As for your button code, you can clean that up using thsi instead:
Private Sub butnok_Click()
 Dim r As Long
 If Worksheets("PPE").FilterMode = True Then Worksheets("PPE").ShowAllData
 r = ActiveCell.Row - (ActiveCell.Row - 1) Mod 4
 Rows(r).Resize(4).Copy
 Rows(r + 4).Resize(4).Insert
 Range("CA" & r + 4 & ":CB" & r + 7).Delete Shift:=xlUp
 Application.CutCopyMode = False
 Range("A" & r + 4).Resize(1, 6).Value = Array(tbclock, "Yes", UCase(tbsname), tbfname, cbotrade, cboarea)
 Unload Me
End Sub

Open in new window


I also added something to fix your columns CA and CB when rows are inserted there so it doesn't mess up your Trade/Work Area boxes.

Matt
0
 

Author Comment

by:Majicthise
ID: 39879309
OK thats very interesting Matt.

I hadn't realised UDF would cause an issue.
I'm not sure that these issue is UDF for formatting.

I see what you've done with the conditional formatting to simplfy it though
And the shifting up of the for rows for the combo box is a clever move too.

But, I am sorry to say that the code update on the button isn't working still.
It's the same sort of issue. Pausing with the selected area highlighted

this code is implemented
 Rows(r + 4).Resize(4).Insert

but it pauses here
 Range("CA" & r + 4 & ":CB" & r + 7).Delete Shift:=xlUp

Most odd.
0
 
LVL 35

Expert Comment

by:mvidas
ID: 39879350
I've never used UDFs in conditional formats before, so I can't say for certain that caused the issue, but I know that when I took your conditional formats out completely the code worked smoothly. It kept stopping on .insert for me (and then usually the whole file would error out and shut excel down) until I got rid of the UDFs from CFs.  I even made a new workbook with a basic UDF and used it in conditional formatting, and it stopped on .insert from a userform too--quite puzzling.

I had made a few other changes to your file (changed userform_activate to _initialize, and changed your worksheet_change event for the date sorting thing), I wonder if one of those made it so the file worked for me.

In any event, I don't like it being so tempermental. Even if you and I both got it working, who is to say a user later down the line won't have problems. Instead of .Insert'ing and .Delete'ing, why not add new users to the bottom of the list?
0
 

Author Comment

by:Majicthise
ID: 39879497
Yes I know what you mean.
In it's current state it's too easy to break...
Too unstable

I did think of copy & pasting at the end and then running the sort routine after.
Would remove the requirement to look after the trade/work area.
I could then run the sort routine after.

I'll have a wee think...

Any other pointers...

I was hoping to highlight a user if the conditional formating was orange (not red)
0
 

Author Closing Comment

by:Majicthise
ID: 39882904
Many thanks for your input.
Got me on the right route thats for sure.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now