Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help please
I have a macro that I'd like to limit the pages it can work on.
Sub columnremove() Dim myRng As Range Set myRng = ActiveSheet.Rows(4) 'for rows that have 0's or blanks in column A 'set myRng = activesheet.range("a:a") With myRng .Replace What:=0, Replacement:="", LookAt:=xlWhole On Error Resume Next .Cells.SpecialCells(xlCellTypeBlanks).EntireColumn .Delete On Error GoTo 0 End With End Sub If i click it, and I am on wrong page it can ruin alot of work, I have only 3 pages it can work on, Month One, Month Two and Month Three. Can this macro be made to "check" if its in one of those pages and not fire, or give an error message if it is not? Ryk |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help please
One way:
Option Explicit Sub columnremove() Dim myRng As Range Select Case LCase(ActiveSheet.Name) Case Is = LCase("Month One"), LCase("Month Two"), LCase("Month Three") Set myRng = ActiveSheet.Rows(4) 'for rows that have 0's or blanks in column A 'set myRng = activesheet.range("a:a") With myRng .Replace What:=0, Replacement:="", LookAt:=xlWhole On Error Resume Next .Cells.SpecialCells(xlCellTypeBlanks).EntireColumn .Delete On Error GoTo 0 End With Case Else MsgBox "Please activate the correct sheet first!" End Select End Sub Ryk wrote: I have a macro that I'd like to limit the pages it can work on. Sub columnremove() Dim myRng As Range Set myRng = ActiveSheet.Rows(4) 'for rows that have 0's or blanks in column A 'set myRng = activesheet.range("a:a") With myRng .Replace What:=0, Replacement:="", LookAt:=xlWhole On Error Resume Next .Cells.SpecialCells(xlCellTypeBlanks).EntireColumn .Delete On Error GoTo 0 End With End Sub If i click it, and I am on wrong page it can ruin alot of work, I have only 3 pages it can work on, Month One, Month Two and Month Three. Can this macro be made to "check" if its in one of those pages and not fire, or give an error message if it is not? Ryk -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help please
Beautiful Dave! Worked like a charm! Hopefully I can sneak in a second request. After adding that and talking with a co-worker, I came to realize we only have one other area to address in making the program safe for multi-user use. I'll be looking into it, but can we make the saved main program open up and right off the bat and request the user save it as a different name so the source stays safe? Maybe make it so they have to save it as a different name or it won't work. I assume I need find start code, and try and get the code to check the filename, if its the source name ask to save, if its not then it opens no problem. Anyways Dave, this is I think your third major help given to me, you make me look awful smart, and that is hard to do. Cheers Dave (AKA Ryk) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help please
If I don't want users to save over my original workbook, I'll use windows
explorer and mark it readonly. It stops most of the problems. But I do keep a backup just in case. But you could use Auto_Open() to do what you want. Option Explicit Sub Auto_Open() Dim Resp As Long Resp = MsgBox(Prompt:="Do you want to save this as a new name?", _ Buttons:=vbYesNo) If Resp = vbYes Then Application.Dialogs(xlDialogSaveAs).Show End If End Sub Ryk wrote: Beautiful Dave! Worked like a charm! Hopefully I can sneak in a second request. After adding that and talking with a co-worker, I came to realize we only have one other area to address in making the program safe for multi-user use. I'll be looking into it, but can we make the saved main program open up and right off the bat and request the user save it as a different name so the source stays safe? Maybe make it so they have to save it as a different name or it won't work. I assume I need find start code, and try and get the code to check the filename, if its the source name ask to save, if its not then it opens no problem. Anyways Dave, this is I think your third major help given to me, you make me look awful smart, and that is hard to do. Cheers Dave (AKA Ryk) -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help please
Dave, if i use that, can i set that to only work on original file? We do alot of work with this, and sometimes its several days of work, I'd hate to have them need save as a different name each time, only wish to protect original. Dave (AKA Ryk) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help please
Opps, as in set it to look for file name as well, like "Hard Copy"? If its Hard Copy, it asks to save as different name, if not it opens fine. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help please
Option Explicit
Sub Auto_Open() Dim Resp As Long if lcase(thisworkbook.name) like "*hard copy*" then Resp = MsgBox(Prompt:="Do you want to save this as a new name?", _ Buttons:=vbYesNo) If Resp = vbYes Then Application.Dialogs(xlDialogSaveAs).Show End If end if End Sub Ryk wrote: Opps, as in set it to look for file name as well, like "Hard Copy"? If its Hard Copy, it asks to save as different name, if not it opens fine. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error when running cut & paste macro | Excel Worksheet Functions | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Highlight Range - wrong macro, please edit. | Excel Worksheet Functions |