Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel code stops

Posted on 2014-02-20
11
Medium Priority
?
380 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 33

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 33

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
 
LVL 35

Accepted Solution

by:
mvidas earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

715 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