Get column letter output from users selection from InputBox

Question

My goal is to output a column letter in a cell.

The sub here asks the user to click on a cell (This cell range address will be the letter I want). Then calls another sub which gets the column letter from the users selection and writes it to the cell.

Dim picker As Range

Set picker = Application.InputBox("Column for total number of bar", "Choose column", Type:=8)

Call WriteColFromPicker(picker, "H19")

This is the called sub:

Sub WriteColFromPicker(pickedRng As Range, targetCell As String)
'Writes the column letter to Form controls - Used in column letter picker

Dim ws As Worksheet: Set ws = Worksheets("Form Controls")

Debug.Print targetCell

Dim chosen As String: chosen = Split(pickedRng.Address, "$")(1)
            ws.Range(targetCell).Value = chosen

End Sub

However, nothing is being written to the cell.

Interestingly, you will see I have a Debug.Print which isn't returning anything...

Probably missing something simple here, but I cannot see it

Another note, it works fine when I dont have it in separate subs, I have split the letter extraction and writing to the cell into a different sub as I intend to call this quite a few times.

Thanks

Your code has broken somewhere before.... probably at Worksheets("Form Controls"). Do you have some On Error Resume Next somewhere before this code? — A.S.H

Vityata Vityata · Accepted Answer · 2017-07-20T16:39:07

I am trying this code in an empty workbook, it works quite ok, printing the column of the selected cell:

Option Explicit

Public Sub TestMe()

    Dim picker As Range    
    Set picker = Application.InputBox("Column for total number of bar", "Choose column", Type:=8)
    Call WriteColFromPicker(picker)

End Sub

Sub WriteColFromPicker(pickedRng As Range)

    Dim ws As Worksheet: Set ws = Worksheets(1)
    Dim chosen As String: chosen = Split(pickedRng.Address, "$")(1)
    Debug.Print chosen

End Sub

Get column letter output from users selection from InputBox

1 Answers