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

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

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

Can you PM an email address to send it to?

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

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.


Trixie;418609 Wrote:
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 :o:

Can you PM an email address to send it to?

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

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

:o: I did warn you that I was a newbie, so untidy, excess 'junk' goes
without saying ;)

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

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

Thanks again for everything you do here for us!


--
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, sometimes people are quick to click the OK button without
reading what it says, you say others are reporting the problem, is it
the same machine? are they using the same logon?


Trixie;418661 Wrote:
:o: I did warn you that I was a newbie, so untidy, excess 'junk' goes
without saying ;)

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

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

Thanks again for everything you do here for us!


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

Simon Lloyd;418664 Wrote:
Trixie, sometimes people are quick to click the OK button without
reading what it says, you say others are reporting the problem, is it
the same machine? are they using the same logon?

Simon,

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


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

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


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

This should do what you need:

Code:
--------------------
Sub hide_every_6th()
Dim i As Long
For i = 9 To 903 Step 6
Rows(i).Hidden = True
Next i
End Sub
--------------------


Trixie;422171 Wrote:
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

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

Simon Lloyd;422341 Wrote:
This should do what you need:

Code:
--------------------
Sub hide_every_6th()
Dim i As Long
For i = 9 To 903 Step 6
Rows(i).Hidden = True
Next i
End Sub
--------------------




Oh my goodness Simon! emb1

You cut a bazillion characters from the macro recording I used down to
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 Tums
to get through the original code I had in the workbook from the macro
recording?

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


Thanks again for helping me with this.


--
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, you will pick up tricks and tips along the way from visiting
forums like this. The macro recorder is a fantastic tool and gets you
off to a good start, although the recorder is very exacting and gives
tons of code you don't really need, try experimenting with things that
you have recorded cutting some items out that you feel may not be doing
whatever task you require.

Glad we could be of help!

Trixie;422591 Wrote:


Oh my goodness Simon! emb1

You cut a bazillion characters from the macro recording I used down to
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 Tums
to get through the original code I had in the workbook from the macro
recording?

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


Thanks again for helping me with this.


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