Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox code
This may not be the most elegant code you've ever seen, but I've discovered
that many other people have struggled with vbCancel in InputBox es, so I'm pretty pleased with what I've come up with your help. With the InputBox, vbCancel and "" both return "False", I set my InputBox with a default string--a useful prompt in a way--that the user must replace with a good sheet name before the macro will work. Leaving the default string results in a msgbox and the InputBox to reload. If the user clears the box and enters "", then the procedure ends as if they'd cancelled. Too bad. I'd be happy to hear if there are ways I can clean this up, though. I always manage to leave room for improvement. Sub InputSheetName() Dim Message As String Dim Title As String Dim wksname Dim wks As Worksheet ' Set prompt message. Message = "Enter a sheet name in MthYYYY format to select the sheet with the exchange rates you wish to use to update the converter." ' Set title. Title = "Select Desired Sheet" ' Display Input Box. wksname = InputBox(Message, Title, "MthYYYY") ' In case user clicks the Okay button before entering a sheet name. If wksname = "MthYYYY" Then MsgBox "Not a valid sheetname. Re-enter a valid name." InputSheetName End If ' In case user clicks the Cancel button. If wksname = "" Then MsgBox ("Ending Macro") End End If ' Error Handler in case user enters and invalid sheet name. On Error Resume Next Set wks = ActiveWorkbook.Worksheets(wksname) On Error GoTo 0 If wks Is Nothing Then MsgBox "Not a valid sheetname. Re-enter a valid name." InputSheetName Else Sheets(wksname).Activate 'Activates the desired worksheet End If ' Selects and copies to the clipboard the desired range of cells on the active sheet. Range("F12:F52").Select Selection.Copy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
InputBox code | Excel Programming | |||
InputBox code | Excel Programming | |||
Inputbox | Excel Programming | |||
inputbox | Excel Programming | |||
Excel Macro Code invoking InputBox. | Excel Programming |