Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
unselecting or uncopying or whatever in VBA
I'm reasonably proficient in VB6 and Access VBA but am an Excel neophyte so
hopefully you can help me. Here's the scenario: I do this to unhide some columns: shtBDA.Columns("A:B").Select If Selection.EntireColumn.Hidden = True Then Selection.EntireColumn.Hidden = False blRehideColumns = True End If then I do a bunch of stuff including this sort of thing: shtBDA.Rows(lngBDAInd).Insert 'insert a new row shtBDA.Rows(lngBDA_START_ROW).Copy shtBDA.Rows(lngBDAInd).PasteSpecial after all that whole bunch of stuff is done I'd like to re-hide the columns like this: shtBDA.Columns("A:B").Select If blRehideColumns Then Selection.EntireColumn.Hidden = True End If but the .Select fails (I reckon) because the Rows...copy has that row selected. I believe this becauseIt doesn't fail if I Debug and stop processing, switch back to the sheet and hit escape to deselect the row and then continue processing. How can I get that selection cleared from the Rows...copy statement? Cheers, Jon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
unselecting or uncopying or whatever in VBA
Application.CutCopyMode = False
Insert this line when you finish your paste actions. "Jon in Canby Or." wrote: I'm reasonably proficient in VB6 and Access VBA but am an Excel neophyte so hopefully you can help me. Here's the scenario: I do this to unhide some columns: shtBDA.Columns("A:B").Select If Selection.EntireColumn.Hidden = True Then Selection.EntireColumn.Hidden = False blRehideColumns = True End If then I do a bunch of stuff including this sort of thing: shtBDA.Rows(lngBDAInd).Insert 'insert a new row shtBDA.Rows(lngBDA_START_ROW).Copy shtBDA.Rows(lngBDAInd).PasteSpecial after all that whole bunch of stuff is done I'd like to re-hide the columns like this: shtBDA.Columns("A:B").Select If blRehideColumns Then Selection.EntireColumn.Hidden = True End If but the .Select fails (I reckon) because the Rows...copy has that row selected. I believe this becauseIt doesn't fail if I Debug and stop processing, switch back to the sheet and hit escape to deselect the row and then continue processing. How can I get that selection cleared from the Rows...copy statement? Cheers, Jon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
unselecting or uncopying or whatever in VBA
"switch back to the sheet and hit escape "
You cannot select "anything" on a sheet unless that sheet is active. The good news is that you don't have to select the sheet or the columns to hide/unhide the columns... shtBDA.Columns("A:B").Hidden = False Also, it is a little more efficient to just hide or unhide the columns without checking their status first. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (I've been to Canby, OR) "Jon in Canby Or." wrote in message I'm reasonably proficient in VB6 and Access VBA but am an Excel neophyte so hopefully you can help me. Here's the scenario: I do this to unhide some columns: shtBDA.Columns("A:B").Select If Selection.EntireColumn.Hidden = True Then Selection.EntireColumn.Hidden = False blRehideColumns = True End If then I do a bunch of stuff including this sort of thing: shtBDA.Rows(lngBDAInd).Insert 'insert a new row shtBDA.Rows(lngBDA_START_ROW).Copy shtBDA.Rows(lngBDAInd).PasteSpecial after all that whole bunch of stuff is done I'd like to re-hide the columns like this: shtBDA.Columns("A:B").Select If blRehideColumns Then Selection.EntireColumn.Hidden = True End If but the .Select fails (I reckon) because the Rows...copy has that row selected. I believe this becauseIt doesn't fail if I Debug and stop processing, switch back to the sheet and hit escape to deselect the row and then continue processing. How can I get that selection cleared from the Rows...copy statement? Cheers, Jon |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
unselecting or uncopying or whatever in VBA
' note this only checks column A as hidden
If shtBDA.Columns("A:B").EntireColumn.Hidden = True Then shtBDA.Columns("A:B").EntireColumn.Hidden = False blRehideColumns = True End If shtBDA.Rows(lngBDAInd).Insert 'insert a new row shtBDA.Rows(lngBDA_START_ROW).Copy shtBDA.Rows(lngBDAInd).PasteSpecial If blRehideColumns Then shtBDA.Columns("A:B").EntireColumn.Hidden = True End If -- Regards, Tom Ogilvy "Jon in Canby Or." wrote in message ... I'm reasonably proficient in VB6 and Access VBA but am an Excel neophyte so hopefully you can help me. Here's the scenario: I do this to unhide some columns: shtBDA.Columns("A:B").Select If Selection.EntireColumn.Hidden = True Then Selection.EntireColumn.Hidden = False blRehideColumns = True End If then I do a bunch of stuff including this sort of thing: shtBDA.Rows(lngBDAInd).Insert 'insert a new row shtBDA.Rows(lngBDA_START_ROW).Copy shtBDA.Rows(lngBDAInd).PasteSpecial after all that whole bunch of stuff is done I'd like to re-hide the columns like this: shtBDA.Columns("A:B").Select If blRehideColumns Then Selection.EntireColumn.Hidden = True End If but the .Select fails (I reckon) because the Rows...copy has that row selected. I believe this becauseIt doesn't fail if I Debug and stop processing, switch back to the sheet and hit escape to deselect the row and then continue processing. How can I get that selection cleared from the Rows...copy statement? Cheers, Jon |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
unselecting or uncopying or whatever in VBA
It sounds like you are asking for Application.CutCopyMode = False (which is
the VBA equivalent to hitting the ESC key after a copy/paste operation), but I couldn't reproduce the error you are getting. Note that it is usually never necessary to select things to work with them (it's also faster to not select them). Also, it avoids errors (for example - VBA is capable of copying data from one sheet to a hidden sheet, but not if you try to select the hidden sheet before pasting the data). Usually you can combine your code on one line by deleting the ".Select" on the first line and the "Selection" on the next line and merge the two lines into one (a simplified starting point for getting away from selecting things). The With statement is also useful when you have several lines that refer to the same object. For example (and it may not be necessary to hide/unhide Columns A-B): With shtBDA.Columns("A:B"). If .EntireColumn.Hidden = True Then .EntireColumn.Hidden = False blRehideColumns = True End If End With With shtBDA .Rows(lngBDAInd).Insert 'insert a new row .Rows(lngBDA_START_ROW).Copy .Rows(lngBDAInd) If blRehideColumns Then _ .Columns("A:B").EntireColumn.Hidden = True End If "Jon in Canby Or." wrote: I'm reasonably proficient in VB6 and Access VBA but am an Excel neophyte so hopefully you can help me. Here's the scenario: I do this to unhide some columns: shtBDA.Columns("A:B").Select If Selection.EntireColumn.Hidden = True Then Selection.EntireColumn.Hidden = False blRehideColumns = True End If then I do a bunch of stuff including this sort of thing: shtBDA.Rows(lngBDAInd).Insert 'insert a new row shtBDA.Rows(lngBDA_START_ROW).Copy shtBDA.Rows(lngBDAInd).PasteSpecial after all that whole bunch of stuff is done I'd like to re-hide the columns like this: shtBDA.Columns("A:B").Select If blRehideColumns Then Selection.EntireColumn.Hidden = True End If but the .Select fails (I reckon) because the Rows...copy has that row selected. I believe this becauseIt doesn't fail if I Debug and stop processing, switch back to the sheet and hit escape to deselect the row and then continue processing. How can I get that selection cleared from the Rows...copy statement? Cheers, Jon |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
unselecting or uncopying or whatever in VBA
Also, it is a little more efficient to just hide or unhide the columns
without checking their status first. My initial thought as well, but then realized he was testing to see how to restore them at the end. -- Regards, Tom Ogilvy "Jim Cone" wrote in message ... "switch back to the sheet and hit escape " You cannot select "anything" on a sheet unless that sheet is active. The good news is that you don't have to select the sheet or the columns to hide/unhide the columns... shtBDA.Columns("A:B").Hidden = False Also, it is a little more efficient to just hide or unhide the columns without checking their status first. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (I've been to Canby, OR) "Jon in Canby Or." wrote in message I'm reasonably proficient in VB6 and Access VBA but am an Excel neophyte so hopefully you can help me. Here's the scenario: I do this to unhide some columns: shtBDA.Columns("A:B").Select If Selection.EntireColumn.Hidden = True Then Selection.EntireColumn.Hidden = False blRehideColumns = True End If then I do a bunch of stuff including this sort of thing: shtBDA.Rows(lngBDAInd).Insert 'insert a new row shtBDA.Rows(lngBDA_START_ROW).Copy shtBDA.Rows(lngBDAInd).PasteSpecial after all that whole bunch of stuff is done I'd like to re-hide the columns like this: shtBDA.Columns("A:B").Select If blRehideColumns Then Selection.EntireColumn.Hidden = True End If but the .Select fails (I reckon) because the Rows...copy has that row selected. I believe this becauseIt doesn't fail if I Debug and stop processing, switch back to the sheet and hit escape to deselect the row and then continue processing. How can I get that selection cleared from the Rows...copy statement? Cheers, Jon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Easy question - Unselecting cells | Excel Programming | |||
Unselecting a Reference | Excel Programming | |||
unselecting not working anymore? | Excel Discussion (Misc queries) | |||
Selecting / UnSelecting sheets | Excel Discussion (Misc queries) | |||
Unselecting a Cell?? | Excel Programming |