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!


--
Trixie

~TRIXIE
------------------------------------------------------------------------
Trixie's Profile: http://www.thecodecage.com/forumz/member.php?userid=438
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116018

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


Trixie;416868 Wrote:
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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116018

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~

Simon Lloyd;416957 Wrote:
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
--------------------



--
Trixie

~TRIXIE
------------------------------------------------------------------------
Trixie's Profile: http://www.thecodecage.com/forumz/member.php?userid=438
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116018

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.


Trixie;417419 Wrote:
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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116018

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;417836 Wrote:
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.




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.



--
Trixie

~TRIXIE
------------------------------------------------------------------------
Trixie's Profile: http://www.thecodecage.com/forumz/member.php?userid=438
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116018

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.

Trixie;418547 Wrote:
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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116018