Solved

Prompt user to enter a column letter using VBA

Posted on 2016-09-26
2
67 Views
Last Modified: 2016-09-26
Dear Experts:

Below Code converts Path Entries in Column 'B' to Hyperlinks. The macro works just great.

Could somebody help me to tweak this code in the following way:

The path entries could be in any column and I do not want to hard code the macro every time the column letter changes (the macro is activated also by other people)

An Input Box is to prompt the user to enter the column letter and then the macro runs.

Only single column letters are allowed, i.e. A till Z. Any other caracter that is entered (numbers, alphanumeric entries, space(s) etc) will trigger an error.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas


Sub Convert_Path_To_Hyperlinks()

Dim r As Range
Dim MyValue As String

On Error Resume Next

   For Each r In Range("B2", Range("B2").End(xlDown))
     
   If r.Value <> "" Then

         MyValue = r.Value

            Range(r.Address).Select

               ActiveSheet.Hyperlinks.Add Anchor:=Excel.Selection, Address:=MyValue, TextToDisplay:=MyValue

     End If

   Next r

    Range("a1").Select

End Sub

Open in new window

0
Comment
Question by:AndreasHermle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41816448
Hi,

pls try

Sub Convert_Path_To_Hyperlinks()

Dim r As Range
Dim MyValue As String

strCol = Application.InputBox("Enter Column", "Column", , , , , , 2)
If Not strCol Like "[A-Za-z]" Then
    MsgBox "Wrong input"
    Exit Sub
End If

On Error Resume Next

   For Each r In Range(Range(strCol & "2"), Range(strCol & "2").End(xlDown))
     
   If r.Value <> "" Then

         MyValue = r.Value

            Range(r.Address).Select

               ActiveSheet.Hyperlinks.Add Anchor:=Excel.Selection, Address:=MyValue, TextToDisplay:=MyValue

     End If

   Next r

    Range("a1").Select

End Sub

Open in new window

Regards
0
 

Author Closing Comment

by:AndreasHermle
ID: 41816516
Great this did the trick. Thank you very much for your great help.

Regards, Andreas
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question