'Enter' key is pasting...

[Trixie]

We use a tool called Macro Express in conjunction with an Excel
workbook, which copies specific Excel data to input into another
application.

I have been enhancing the user workbook using some
formulas/validation/VBA code/macros, and now I am having an issue with
the Enter function.

Once all work has been completed in the workbook, a macro is run to
copy the worksheet, paste the values, remove validation & formats
everything as text (this is done with an excel macro tied to a button
the user clicks..i'm sure there is an easier, cleaner approach, but hey,
i'm a newbie yet).

The user then saves the workbook, exits and re-opens, disabling the
macros.

They then activate the ME macro to start the copy process. As the copy
function comes to the end of a row, when the Enter command is called to
go back to column A in the next row, it is now pasting the last piece of
copied data into the next Excel cell.

Is there some setting within Excel that would change the functionality
of the Enter key to paste?

The last cell in the row did have a DV list attached to the cell prior
to running the macro to remove it...did it leave a 'ghost'?

Thanks!

 

[Simon Lloyd]

Trixie, try running these one after the other and then let us know if
everything is back to normal.


Code:
--------------------
Sub clear_enter()
Application.OnKey "{ENTER}", ""
Application.OnKey "~", ""
End Sub


Sub enable_enter()
Application.OnKey "{ENTER}"
Application.OnKey "~"
End Sub
--------------------

We use a tool called Macro Express in conjunction with an Excel
workbook, which copies specific Excel data to input into another
application.

I have been enhancing the user workbook using some
formulas/validation/VBA code/macros, and now I am having an issue with
the Enter function.

Once all work has been completed in the workbook, a macro is run to
copy the worksheet, paste the values, remove validation & formats
everything as text (this is done with an excel macro tied to a button
the user clicks..i'm sure there is an easier, cleaner approach, but hey,
i'm a newbie yet).

The user then saves the workbook, exits and re-opens, disabling the
macros.

They then activate the ME macro to start the copy process. As the copy
function comes to the end of a row, when the Enter command is called to
go back to column A in the next row, it is now pasting the last piece of
copied data into the next Excel cell.

Is there some setting within Excel that would change the functionality
of the Enter key to paste?

The last cell in the row did have a DV list attached to the cell prior
to running the macro to remove it...did it leave a 'ghost'?

Thanks!

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Trixie]

Hi Simon,

I ran the code you posted in the This Workbook module but am stil
having the issue occur.

I should note that this is only occurring on one of the worksheet
within the workbook. I have two other worksheets where I basically d
the same thing, jsut structured differently, but those seem to work jus
fine. It's just the one that had validation in the last column.

Here is the code that is behind my SetValues button~remember, newbie
be kind :Blink:

Private Sub SetValues141N_Click()
Response = MsgBox("Setting values removes automation, are you sur
you want to set values now?", 36)
If Response = vbNo Then
Exit Sub
End If

Cells.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Range("G2").Select
Application.CutCopyMode = False
Selection.ClearContents

Cells.Select
Selection.NumberFormat = "@"

MsgBox "Values have been set", 64

End Sub

I really hate to lose all of the 'smarts' in the workbook...this i
about 3295 cells worth of data that would have to be manuall
entered~both in the workbook, and the additional application.

I appreciate all that you guys do for us here!

Thanks~

 

[Simon Lloyd]

I can see nothing wrong with that code, can you post the offending
workbook?

Providing a workbook will not only get you your answer quicker but will
better illustrate your problem, usually when we can see your data (-it
can be dummy data but must be of the same type-) and your structure it
is far easier for us to give you a tailored, workable answer to your
query

Attachments.

To upload a workbook, click reply then add your few words, scroll down
past the submit button and you will see the Manage Attachments button,
this is where you get to add files for upload, if you have any trouble
please use this link or the one at the bottom of the
any page.

Hi Simon,

I ran the code you posted in the This Workbook module but am still
having the issue occur.

I should note that this is only occurring on one of the worksheets
within the workbook. I have two other worksheets where I basically do
the same thing, jsut structured differently, but those seem to work just
fine. It's just the one that had validation in the last column.

Here is the code that is behind my SetValues button~remember, newbie,
be kind :Blink:

Private Sub SetValues141N_Click()
Response = MsgBox("Setting values removes automation, are you sure you
want to set values now?", 36)
If Response = vbNo Then
Exit Sub
End If

Cells.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator
_
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G2").Select
Application.CutCopyMode = False
Selection.ClearContents

Cells.Select
Selection.NumberFormat = "@"

MsgBox "Values have been set", 64

End Sub

I really hate to lose all of the 'smarts' in the workbook...this is
about 3295 cells worth of data that would have to be manually
entered~both in the workbook, and the additional application.

I appreciate all that you guys do for us here!

Thanks~

Attachments.

To upload a workbook, click reply then add your few words, scroll down
past the submit button and you will see the Manage Attachments button,
this is where you get to add files for upload, if you have any trouble
please use this link or the one at the bottom of the
any page.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Trixie]

Hi Simon,

I uploaded 'The Bad Boy' (http://www.sendspace.com/file/ixmpl5) t
Sendspace.

It's Sheet8 that is the problem child. The Macro Express proces
copies & stores each cell between A6 & P6, tabs through to Q6 and the
executes the enter function to move back to copy same for rows 7, 8 &
before moving to the next application to paste the data. Instead o
entering(carriage return) it pastes the last piece of data copied in Q
and then runs amok.

The Macro Express logic has been in place since last July, so I know i
was working fine before I started automating some of the workbook.

Thanks for your help!

I can see nothing wrong with that code, can you post the offendin
workbook?

Providing a workbook will not only get you your answer quicker but wil
better illustrate your problem, usually when we can see your data (-i
can be dummy data but must be of the same type-) and your structure i
is far easier for us to give you a tailored, workable answer to you
query

Attachments.

To upload a workbook, click reply then add your few words, scroll dow
past the submit button and you will see the Manage Attachments button
this is where you get to add files for upload, if you have any troubl
please use this link or the one at the bottom of th
any page.




Attachments.

To upload a workbook, click reply then add your few words, scroll dow
past the submit button and you will see the Manage Attachments button
this is where you get to add files for upload, if you have any troubl
please use this link or the one at the bottom of th
any page.

 

[Simon Lloyd]

Trixie, no disrespect but you need to attach the workbook here as right
now i am behind a strict firewall so cannot download your workbook.

Hi Simon,

I uploaded 'The Bad Boy' (http://www.sendspace.com/file/ixmpl5) to
Sendspace.

It's Sheet8 that is the problem child. The Macro Express process copies
& stores each cell between A6 & P6, tabs through to Q6 and then executes
the enter function to move back to copy same for rows 7, 8 & 9 before
moving to the next application to paste the data. Instead of
entering(carriage return) it pastes the last piece of data copied in Q6
and then runs amok.

The Macro Express logic has been in place since last July, so I know it
was working fine before I started automating some of the workbook.

Thanks for your help!

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Trixie]

Trixie, no disrespect but you need to attach the workbook here as righ
now i am behind a strict firewall so cannot download your workbook.

Sorry Simon...I keep getting an error...

Connection Interrupted
The connection to the server was reset while the page was loading.
The network link was interrupted while negotiating a connection. Pleas
try again.

and I know that a lot of forums look at attachments as taking u
valuable server space :

Can you PM an email address to send it to?

Thanks

 

[Simon Lloyd]

Trixie, the problem is your end, i have just uploaded an attachment to
your post.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Simon Lloyd]

Trixie, apart from a whole load of code that could go and some that can
be tidied there's nothing wrong with it, there may be some code in your
PERSONAL.xls which is causing this error, to find it it may be in one of
these locations c:\program files\microsoft office\office10\excel\xlstart
OR
c:\program files\microsoft office\office\excel11\xlstart, take a look
at the VBE and see if there is any code in there at all.

Sorry Simon...I keep getting an error...

Connection Interrupted
The connection to the server was reset while the page was loading.
The network link was interrupted while negotiating a connection. Please
try again.

and I know that a lot of forums look at attachments as taking up
valuable server space :

Can you PM an email address to send it to?

Thanks~

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Trixie]

Trixie, apart from a whole load of code that could go and some that ca
be tidied there's nothing wrong with it, there may be some code in you
PERSONAL.xls which is causing this error, to find it it may be in one o
these locations c:\program files\microsoft office\office10\excel\xlstar
OR
c:\program files\microsoft office\office\excel11\xlstart, take a loo
at the VBE and see if there is any code in there at all.

: I did warn you that I was a newbie, so untidy, excess 'junk' goe
without saying

I will be picking up my copy of VBA for Excel for Dummies shortl
though...in some cases the best I could do for now was to record a macr
and use that with my controls, etc.

I appreciate you taking a look at it, and I will check those file
tomorrow when I get to work. I didn't think to go there, because othe
users were the ones reporting the issue to me and they wouldn't have
clue about personal workbooks.

Thanks again for everything you do here for us

 

[Simon Lloyd]

Trixie, sometimes people are quick to click the OK button withou
reading what it says, you say others are reporting the problem, is i
the same machine? are they using the same logon

: I did warn you that I was a newbie, so untidy, excess 'junk' goe
without saying

I will be picking up my copy of VBA for Excel for Dummies shortl
though...in some cases the best I could do for now was to record a macr
and use that with my controls, etc

I appreciate you taking a look at it, and I will check those file
tomorrow when I get to work. I didn't think to go there, because othe
users were the ones reporting the issue to me and they wouldn't have
clue about personal workbooks

Thanks again for everything you do here for us

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Trixie]

Trixie, sometimes people are quick to click the OK button withou
reading what it says, you say others are reporting the problem, is i
the same machine? are they using the same logon?

Simon,

No, the template is stored in a share location, but everyone opens an
does a 'save as' to their own hard drive while working. They all hav
their own login ids as well

 

[Trixie]

Trixie, apart from a whole load of code that could go and some that can
be tidied there's nothing wrong with it, there may be some code in your
PERSONAL.xls which is causing this error, to find it it may be in one of
these locations c:\program files\microsoft office\office10\excel\xlstart
OR
c:\program files\microsoft office\office\excel11\xlstart, take a look
at the VBE and see if there is any code in there at all.

On the subject of tidying up, there was a lengthy piece of code, which
was a macro I recorded that basically hides every 6th row (starting at
row 9 and continuing through row 903 when a click command is entered.
Is there a cleaner way to do this than the way the macro records it?

If needed, I can copy here.

Thanks!

 

[Simon Lloyd]

This should do what you need

Code
-------------------
Sub hide_every_6th(
Dim i As Lon
For i = 9 To 903 Step
Rows(i).Hidden = Tru
Next
End Su
-------------------

On the subject of tidying up, there was a lengthy piece of code, whic
was a macro I recorded that basically hides every 6th row (starting a
row 9 and continuing through row 903 when a click command is entered. I
there a cleaner way to do this than the way the macro records it

If needed, I can copy here

Thanks

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Trixie]

This should do what you need: Code
-------------------
Dim i As Long
For i = 9 To 903 Step 6
Rows(i).Hidden = True
Next i
End Su -------------------

Oh my goodness Simon! emb1

You cut a bazillion characters from the macro recording I used down t
just that...where's the darned smilie with the super-hero cape?

How many times did you roll your eyes, shake your head and pop a Tum
to get through the original code I had in the workbook from the macr
recording?

Newbies wko
Although, I was able to add a msgbox and end with the cursor in A2 a
the end of the code...all by myself!


Thanks again for helping me with this

 

[Simon Lloyd]

Trixie, you will pick up tricks and tips along the way from visitin
forums like this. The macro recorder is a fantastic tool and gets yo
off to a good start, although the recorder is very exacting and give
tons of code you don't really need, try experimenting with things tha
you have recorded cutting some items out that you feel may not be doin
whatever task you require

Glad we could be of help!

:eek

Oh my goodness Simon! emb

You cut a bazillion characters from the macro recording I used down t
just that...where's the darned smilie with the super-hero cape

How many times did you roll your eyes, shake your head and pop a Tum
to get through the original code I had in the workbook from the macr
recording

Newbies wk
Although, I was able to add a msgbox and end with the cursor in A2 a
the end of the code...all by myself


Thanks again for helping me with this

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com