Posts by honoliipali

Re: Pause Macro - Select From A Drop Down - Restart Macro

Thank you both but I am a newbie and don't understand either suggestion. I am intrigued by the ComboBox but can't seem to find any information that addresses anything like what I want to do.

[COLOR="Blue"]I am trying to pause a macro on a protected sheet, select 2 adjacent cells (initially protected), utilize an existing drop down box to select a name from the list, copy the name from the list into the range of cells, then re-start the macro.
I had no problem when there was just one name (see John Smith below).
I tried to use the InputBox command but needed to actually type in the name.
Any ideas as to how to do what I need?

Thank you all.[/COLOR]

ActiveSheet.Unprotect
    Range("C27:D27").Select
    'ActiveCell.FormulaR1C1 = "John Smith"
   ' Application.CutCopyMode = False
   ' ActiveSheet.Paste
    'Range("c27:d27") = InputBox("Enter value")

Re: Save As Error Handling Through Input Box

Thanks for sticking this out with me. I have a working prototype and have put it into use. I will continue to work on how to clear the default filename or disable the command bar save functions.

Re: Save As Error Handling Through Input Box

Very simple solution Robert. It does what I need it to do.:)

Is there a way that I can clear the default filename so they don't over write the original? The Excel workbook works by the user opening the file and filling in a bunch of unprotected cells. The problem I have is that some users don't take the time to enter a unique filename.

Thank You - this forum is great and allows me to learn quickly.

Re: Save As Error Handling Through Input Box

Robert - I input your code but I get the following failure when clicking on No or Cancel:
Run-time error '1004':
Method 'SaveAs' of object '-Workbook' failed

I need to:

• Turn off "save" and "save as" command bars
• Prompt user for a unique filename
• Save file to a specific directory on the network common to all "p:\data\prc"
• Requery user if filename exists and if they DON'T want to overwrite.
• Return to worksheet (there is only one) if the user cancels from the message box.
• Turn commandbars back on

The macro runs from an on screen button
I get bug errors on NO or CANCEL when clicked in the message box
This is my current code:

Private Sub Workbook_Open()
    Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save As...").Enabled = False
    Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = False
End Sub


Sub SaveMe()
'
' Keyboard Shortcut: Ctrl+z
'
    Dim BaseDir As String
    Dim NewName As String
    BaseDir = "p:\data\prc"
    On Error Resume Next
    'Application.DisplayAlerts = False
    ChDir BaseDir
    
    NewName = Application.InputBox(Prompt:="Please enter a UNIQUE filename without the .xls.", Type:=1 + 2)
    On Error GoTo 0
    ActiveWorkbook.SaveAs NewName
     
    'Application.DisplayAlerts = True
    Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save As...").Enabled = True
    Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = True
End Sub

I am a very proficient user of Excel but just starting out with VBA. Thank You very much.