Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Prompt user to enter a column letter using VBA

Posted on 2016-09-26
2
Medium Priority
?
79 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
2 Comments
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

926 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