Creating an Input Box Error Message When Header is Not Selected?

[Jambi46n2]

Hello,

I have created a code in VBA to convert dates from YMD to MDY as a Ribbon (Excel 2007)

Problem is the code doesn't work unless the user specifies the Cell of the Header for the Column to be Converted. I would like to place a Message Box if someone typed C2 instead of C1 for example. So I can prompt the user and remind them to select a Column Header.

Here's my code that works perfectly fine with an exception to having a Message Box Error.

As always thanks for your help!

Sub Convert_Dates_YMD_To_MDY(control As IRibbonControl)'
' Convert_Dates_YMD_To_MDY


  Dim aRange As Range
      
  On Error Resume Next
  Set aRange = Application.InputBox(prompt:="Enter The First Cell of Column to Convert (Example C1)", Type:=8)
  If aRange Is Nothing Then
    MsgBox "Operation Cancelled"
  
  Else
    aRange.Select
  End If


    Range(Selection, Selection.EntireColumn).Select
    Selection.TextToColumns Destination:=aRange, DataType:=xlDelimited, _
    TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 5), TrailingMinusNumbers:=True


End Sub

 

[dmt32]

Hi,
should just be able to use selection in your code to specify required column & avoid the error.

Not tested but see if this update to your code helps:

Sub Convert_Dates_YMD_To_MDY(control As IRibbonControl) '
' Convert_Dates_YMD_To_MDY


  Dim aRange As Range
      
  On Error Resume Next
  Set aRange = Application.InputBox(prompt:="Enter The First Cell of Column to Convert (Example C1)", Type:=8)
    If aRange Is Nothing Then MsgBox "Operation Cancelled", 48, "Cancelled": Exit Sub
  
    Columns(aRange.Column).TextToColumns Destination:=aRange, DataType:=xlDelimited, _
    TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 5), TrailingMinusNumbers:=True




End Sub

Dave

 

[Jambi46n2]

Thanks for the reply.

Unfortunately that didn't work.

I should clarify I don't have any issues or errors with my code, I want an error message to show when an incorrect value is selected.

I only want column headers to be selected, and an error message if anything below Row 1 is placed in the input box.

Thank you again for taking the time.