 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / March 2008assigning macro
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? If TypeName(Selection) <> "Range" Then MsgBox "Please select some cells. " Exit Sub End if  SignatureJim 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? I'll try that Jim. Between what lines of my macro would I insert those 4 lines? > > If TypeName(Selection) <> "Range" Then > MsgBox "Please select some cells. " > Exit Sub > End if 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? "Jim Cone" wrote: > If TypeName(Selection) <> "Range" Then > MsgBox "Please select some cells. " > Exit Sub > End if Thanks Jim....I'll be trying that sometime during the day, hope it works. Much appreciated. > Immediately above "Selection.Cut" > Jim Cone [quoted text clipped - 9 lines] > > Exit Sub > > End if 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 > > If TypeName(Selection) <> "Range" Then > MsgBox "Please select some cells. " > Exit Sub > End if 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 SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >I assigned macros to texts boxes on the sheets in an excel workbook, so >that [quoted text clipped - 30 lines] > What row of code can I add to prevent this, or how can I otherwise prevent > this from happening? 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? > 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 [quoted text clipped - 45 lines] > > What row of code can I add to prevent this, or how can I otherwise prevent > > this from happening? 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? SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > I'm not really sure what you're asking Don, but what the user will be > doing [quoted text clipped - 66 lines] >> > prevent >> > this from happening? 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. > How do you not have something selected? > You failed to post your macro so I proposed a macro that should work. [quoted text clipped - 74 lines] > >> > prevent > >> > this from happening? >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 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 SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Don - again, I'm having difficulty understanding what you are getting > at... [quoted text clipped - 137 lines] >> >> > prevent >> >> > this from happening? 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 > >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 [quoted text clipped - 173 lines] > >> >> > prevent > >> >> > this from happening? 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. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Jim, The premise of your code below works really well, but now, since I > added [quoted text clipped - 257 lines] >> >> >> > prevent >> >> >> > this from happening? BTW, I am in North Austin by the Lake Travis dam. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > > Is this answer to MY post replying to Jim your way of saying you do NOT [quoted text clipped - 274 lines] >>> >> >> > prevent >>> >> >> > this from happening? 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. > BTW, I am in North Austin by the Lake Travis dam. > [quoted text clipped - 246 lines] > >>> >> >> > > >>> >> >> > Sub sheetabck2sheetdef() 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? SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Good Heavens, I really don't care who's post I'm replying to, I'm simply > looking for an answer. [quoted text clipped - 298 lines] >> >>> >> >> > >> >>> >> >> > Sub sheetabck2sheetdef() 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. > 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 [quoted text clipped - 247 lines] > >> >>> >> > to > >> >>> >> > all All I can say is, "good luck" SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > All due respect Don, you are being deliberately obtuse. > [quoted text clipped - 304 lines] >> >> >>> >> > to >> >> >>> >> > all |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.