assigning macro

C

carrera

I assigned macros to texts boxes on the sheets in an excel workbook, so that
inexperienced computer users can highlight particular rows on one worksheet,
and move them to another.
Here is an example of a macro, moving information from "sheet abc" to "sheet
def"...

Sub sheetabck2sheetdef()
'
' sheetabc2sheetdef Macro
' Macro recorded 3/13/2008 by userx
'

'
Selection.Cut
Sheets("sheet def").Select
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("sheet abc").Select
Selection.Delete Shift:=xlUp
Range("A14:A15").Select
Sheets("sheet def").Select
Cells(Rows.Count, "A").End(xlUp).Offset(0, 0).Select
End Sub


My problem: I notice if I accidently click a text box the macro was assigned
to, and no rows were selected, the actual text box transfers to the other
sheet.

What row of code can I add to prevent this, or how can I otherwise prevent
this from happening?
 
J

Jim Cone

If TypeName(Selection) <> "Range" Then
MsgBox "Please select some cells. "
Exit Sub
End if
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"carrera"
wrote in message
I assigned macros to texts boxes on the sheets in an excel workbook, so that
inexperienced computer users can highlight particular rows on one worksheet,
and move them to another.
Here is an example of a macro, moving information from "sheet abc" to "sheet
def"...

Sub sheetabck2sheetdef()
'
' sheetabc2sheetdef Macro
' Macro recorded 3/13/2008 by userx
'
Selection.Cut
Sheets("sheet def").Select
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("sheet abc").Select
Selection.Delete Shift:=xlUp
Range("A14:A15").Select
Sheets("sheet def").Select
Cells(Rows.Count, "A").End(xlUp).Offset(0, 0).Select
End Sub


My problem: I notice if I accidently click a text box the macro was assigned
to, and no rows were selected, the actual text box transfers to the other
sheet.
What row of code can I add to prevent this, or how can I otherwise prevent
this from happening?
 
D

Don Guillett

You didn't say anything about ENTIRE rows so if you don't have more than ONE
ROW selected this will quit. If you do have more than one row selected it
will copy that selection of cells to the next available row on the ds and
delete the selected rows on the source sheet. Is that what is needed?

Sub MoveSelection()
With Selection
If .Rows.Count < 2 Then Exit Sub
Set ds = Sheets("sheet def") 'Destination sheet
dlr = ds.Cells(Rows.Count, "a").End(xlUp).Row + 1
.Copy ds.Cells(dlr, "A")
.Delete Shift:=xlUp
End With
End Sub
 
J

Jim Cone

Immediately above "Selection.Cut"
Jim Cone


"carrera"
wrote in message
I'll try that Jim.
Between what lines of my macro would I insert those 4 lines?
 
C

carrera

I'm not really sure what you're asking Don, but what the user will be doing
is selecting 2 entire rows, and then clicking on 1 of 11 text boxes to send
the 2 selcted rows to the other page.

I already have the macro(s) I mentioned in my first post assigned to all the
text boxes on each of the 11 sheets (121 text boxes in total), so, if what
you are suggesting I change all those macros to the one you have
written....I'm not going to do that.

The solution Jim Cone presented looks like it would work, I'm just not sure
where to insert it in the macros.

Can you please inform?
 
C

carrera

Thanks Jim....I'll be trying that sometime during the day, hope it works.

Much appreciated.
 
D

Don Guillett

How do you not have something selected?
You failed to post your macro so I proposed a macro that should work.
Let's see. Changing macros or inserting lines into existing.??
Which brings up another question. Why do you have more than one macro when
one should do it?
Perhaps it could be assigned to a custom button and used anywhere without
buttons or text boxes?
 
C

carrera

Don - again, I'm having difficulty understanding what you are getting at...

How do I not have something selected?....well, I'm at a loss as to what you
mean. As I explained before, when I select the 2 rows I want to move to
another sheet, and click on the text box with the macro assigned to it,
everything works fine. However, if, playing devils advocate, I do something
that the end user may very well do, i.e. click on the text box with the macro
assigned to it Without choosing 2 rows, the text box itself moves to the
other page. I can't explain it any better than that, because that is what
happens.

You failed to post your macro?.....Well, unless we are talking about
different things, yes, I did post my macro....right in my first post.

Let's see, changing macros or inserting lines into existing?....I suppose
you're using sarcasm there, but I'm certainly not understanding it. Are you
saying one is better than the other? I'm not a mind reader doug, I can't tell
what you are saying unless you say it.

Why do I have more than one macro?....Well, I suppose because I'm not an MVP
in Excel, which is why I'm here asking a question. I have more than 1 macro,
because it's the only way I could figure to do it. On each of my sheets I
have a choice of sending 2 rows to one of eleven different sheets, so I have
eleven macros on each sheet, each macro assisgned to a text box, so the end
user can click on the text box with the name of the sheet where they want the
cells to go. Each of those sheets needs 11 buttons. 11 x's 11 is 121 mcaro's
is 121 marco assigned text boxes for the end user to click. If that is a
stupid headed way to do it, then I'm stupid, but it's the only way I knew.

Perhaps it could be assigned to a custom button, etc....?.....the end users
will be people who have no knowledge at all of excel, so I thought the
easiest way would be for them to have boxes to click on to perform the task
they need to perform.

I hope that answered all your questions, and if you have any more for me,
I'd appreciate it if you could ask them in such a way that you realize I'm
not an MVP like you.

Just some slob trying to make something work, and don't want to change
horses 99% of the way through the task.
 
D

Don Guillett

How do I not have something selected?....well, I'm at a loss as to what you
mean. As I explained before, when I select the 2 rows I want to move to
Click to expand...
try assigning this to a text box and see what happens
sub whycantinotselectsomething()
msgbox selection.address
end sub

Sub wcinss()' now try Jim's
If TypeName(Selection) <> "Range" Then
MsgBox "Please select some cells. "
Exit Sub
End If
MsgBox Selection.Address
End Sub


====
You did post one macro. Didn't you say you had 11 macros for each of 11
sheets=121 macros? What do each do. Send your workbook directly to me and I
will have a look at fixing it to do what you need. Your macro is selecting
when it is NOT necessary and makes things "jump around" in the absence of a
line to suspend screen updating. Cut and copy do the same thing when you
come back to delete the rows. Are the end users selecting the row numbers or
a cell(s) within the row? What determines which sheet and range the data is
moved to? As I said, it certainly can be made more efficient. If you like,
send the workbook. We were all slobs at one time.

Selection.Cut
Sheets("sheet def").Select
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("sheet abc").Select
Selection.Delete Shift:=xlUp
Range("A14:A15").Select
Sheets("sheet def").Select
Cells(Rows.Count, "A").End(xlUp).Offset(0, 0).Select
 
C

carrera

Jim, The premise of your code below works really well, but now, since I added
it in front of the rest of the macro, it still gives me the "select some
cells" message if I select 2 rows to transfer. What do I need to put in, and
where to turn off that message and perfrom the rest of the macro.

Here is exactly what my macro looks like now. Can you tell me what is wrong?
In the line below that reads....
If SouthAustin <> " " Then

SouthAustin is the text in the text box I have the macro assigned to....You
had said

If TypeName(Selection) <> "Range" Then

I guess that is wrong, but I didn't know what to replace
TypeName(Seclection) and "Range" with.

I do appreciate your help, I'm just have trouble putting the correct word in
there.

Thanks
carrera




Sub Seton2SAustin()
'
' Seton2SAustin Macro
' Macro recorded 3/13/2008 by UserX
'

'
If SouthAustin <> " " Then
MsgBox "MUST SELECT 2 ROWS"
Exit Sub
End If

Selection.Cut
Sheets("SAustin").Select
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Seton").Select
Selection.Delete Shift:=xlUp
Range("A14:A15").Select
Sheets("SAustin").Select
Cells(Rows.Count, "A").End(xlUp).Offset(0, 0).Select
End Sub
 
C

carrera

Jim, The premise of your code below works really well, but now, since I added
it in front of the rest of the macro, it still gives me the "select some
cells" message if I select 2 rows to transfer. What do I need to put in, and
where to turn off that message and perfrom the rest of the macro.

Here is exactly what my macro looks like now (see end of message). Can you
tell me what is wrong?
In the line below that reads....
If SouthAustin <> " " Then

SouthAustin is the text in the text box I have the macro assigned to....You
had said

If TypeName(Selection) <> "Range" Then

I guess that is wrong, but I didn't know what to replace
TypeName(Seclection) and "Range" with.

I do appreciate your help, I'm just have trouble putting the correct word in
there.

Thanks
carrera

Here's the macro....


Sub Seton2SAustin()
'
' Seton2SAustin Macro
' Macro recorded 3/13/2008 by UserX
'

'
If SouthAustin <> " " Then
MsgBox "MUST SELECT 2 ROWS"
Exit Sub
End If

Selection.Cut
Sheets("SAustin").Select
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Seton").Select
Selection.Delete Shift:=xlUp
Range("A14:A15").Select
Sheets("SAustin").Select
Cells(Rows.Count, "A").End(xlUp).Offset(0, 0).Select
End Sub
 
D

Don Guillett

Is this answer to MY post replying to Jim your way of saying you do NOT want
my help? You did NOT follow Jim's instructions. Instead you tried to
substitute your own ideas.
 
C

carrera

Good Heavens, I really don't care who's post I'm replying to, I'm simply
looking for an answer.

I acknowledged I didn't put the correct information in the code Jim posted,
when I put it in exactly how he posted it, it did not work....I tried to used
some logic and judgement by replacing components I thought my be the problem
and they did not work. I'm simply trying to ascertain what will work.
If you know what I should be putting in there, feel free to advise. It
doesn't make any difference if it's from you or the Queen of England, I'd be
just as grateful and say thank you either way.
 
D

Don Guillett

Read my entire postings. Unless it is fully understood what you are trying
to do it's hard to help. Did I not suggest you send me a workbook to look
at?
 
C

carrera

All due respect Don, you are being deliberately obtuse.

I have explained in detail what my goals are, and if I can't understand you
questions, perhaps it because you are asking me things I've either already
explained, or have no bearing.

I am not going to send you my private files, and was frankly surprised you
asked for them. Apparantly Jim knew what I was getting at, and had no trouble
responding.
That is perhaps why I was addressing him, since I was already on his trail.

I've had a lot of success on this board getting answers to my questions,
however, not in this case.

I suppose one cannot bat 1000.
I'm confident the problem will eventually be resolved.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro giving me grief.. 2
Help with Code 1
Copy and Paste 1
Delete row macro 3
Cell in row = 0 delete 1
Row value zero 3
2 x work sheet change???? 3
Relative Cell position NOT working with or without macro 6

Top