Solved

Prompt user to enter a column letter using VBA

Posted on 2016-09-26
2
55 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 49

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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