I need a text box and can't find one

[DKY]

I'm sure this website alone has examples of these and maybe I'm just using the wrong search terms. I need to put, in the middle of my macro, a box that pops up and lets you pick a column, whether from a drop down or just a simple text box itself. I can't find any exaples of textboxes though. Help?

[Tom Ogilvy]

Dim rng as Range
On Error Resume Next
set rng = Application.InputBox("Select column with mouse",Type:=8)
On Error goto 0
if rng is nothing then
msgbox "You cancelled"
exit sub
End if
rng.EntireColumn.select
msgbox "You selected " & rng.EntireColumn.Address(0,0)

--
Regards,
Tom Ogilvy

"DKY" <DKY.1rri3h_1120676887.0831@excelforum-nospam.com> wrote in message
news:DKY.1rri3h_1120676887.0831@excelforum-nospam.com...
>
> I'm sure this website alone has examples of these and maybe I'm just
> using the wrong search terms. I need to put, in the middle of my
> macro, a box that pops up and lets you pick a column, whether from a
> drop down or just a simple text box itself. I can't find any exaples
> of textboxes though. Help?
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=385000
>

[DKY]

Thank you Tom. Sorry about my late response but I finally got around to trying this out. I wanted to tie this in with a script that I already have that looks like so.

Sub Spaces()
'
' spaces Macro

Dim LastRow As Long
Dim i As Long
LastRow = Range("B65536").End(xlUp).Row
Application.ScreenUpdating = False
For i = LastRow To 2 Step -1
If Range("A" & i).Value <> _
Range("A" & i - 1).Value Then
Range("A" & i).EntireRow.Insert
End If
Next 'i
Application.ScreenUpdating = True

Rows("2:2").Select
Range("A2").Activate
Selection.Interior.ColorIndex = xlNone

End Sub

So I took what you gave me above and put the column selection in a variable so now it looks like so.

Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Select column with mouse", Type:=8)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "You cancelled"
Exit Sub
End If
rng.EntireColumn.Select
Dim col
col = rng.EntireColumn.Address(0, 0)
MsgBox "You selected " & col

But I don't know where to tie in the col variable. I should have asked this in the first question but I thought if I could just get some direction I wouldn't be bothering everyone and I could figure it out on my own. Guess I was wrong

Anyway. I tried replacing the "A" with col but it gives me errors. Any advice?

[DKY]

::bump::
::bump::

[Norman Jones]

Hi DKY,

Perhaps:

rng.EntireColumn.Cells(2).Interior.ColorIndex = xlNone

---
Regards,
Norman



"DKY" <DKY.1s9k2b_1121519103.4551@excelforum-nospam.com> wrote in message
news:DKY.1s9k2b_1121519103.4551@excelforum-nospam.com...
>
> ::bump::
> ::bump::
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=385000
>

[DKY]

I'm revisiting this and have come up with the following:

Please Login or Register  to view this content.

Problem is that its stopping at this code here

Please Login or Register  to view this content.

and giving me the following:
"Object variable or With block variable not set"

Any clues or hints?

One other thing, is there an easier way to write this?

[Bernie Deitrick]

Change
Dim LastRow As Range
to
Dim LastRow As Long

HTH,
Bernie
MS Excel MVP


"DKY" <DKY.1ul4el_1125417938.9036@excelforum-nospam.com> wrote in message
news:DKY.1ul4el_1125417938.9036@excelforum-nospam.com...
>
> I'm revisiting this and have come up with the following:
>
> Code:
> --------------------
> Option Explicit
>
> Sub INSERT_SPACES_TXTBOX()
> Dim LastRow As Range
> Dim Rng As Range
> Dim i As Long
> On Error Resume Next
> Set Rng = Application.InputBox("Select column with mouse", Type:=8)
> On Error GoTo 0
> If Rng Is Nothing Then
> MsgBox "You cancelled"
> Exit Sub
> End If
> 'Rng.EntireColumn.Select
> 'MsgBox "You selected " & Rng.EntireColumn.Address(0, 0)
>
> Dim strfirst As String
> Dim strletter As String
>
> strfirst = IIf(Chr$(64 + Rng.Column \ 26) = "@", "", Chr(64 + Rng.Column \ 26))
> strletter = strfirst & Chr$(64 + Rng.Column Mod 26)
> 'MsgBox strletter
>
> LastRow = Range(strletter & "65536").End(xlUp).Row
>
> For i = LastRow To 2 Step -1
> If Range(strletter & i).Value <> Range(strletter & i - 1).Value Then
> Range(strletter & i).EntireRow.Insert
> End If
> Next
>
> Rows("2:2").Select
> Range("A2").Activate
> Selection.Interior.ColorIndex = xlNone
>
> End Sub
> --------------------
>
>
> Problem is that its stopping at this code here
>
> Code:
> --------------------
> LastRow = Range(strletter & "65536").End(xlUp).Row
> --------------------
>
> and giving me the following:
> "Object variable or With block variable not set"
>
> Any clues or hints?
>
> One other thing, is there an easier way to write this?
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=385000
>

[DKY]

Thanks Bernie, that works beautifully!

Is there a way, or even a need to recode this and make it cleaner and maybe easier to understand? I kinda took from all kinds of snippets on the net.

Please Login or Register  to view this content.

[Bernie Deitrick]

Sub Insert_Spaces_Txtbox()
Dim LastRow As Long
Dim Rng As Range
Dim myCol As Integer
Dim i As Long

' Select column inputbox
On Error Resume Next
Set Rng = Application.InputBox("Select column with mouse", Type:=8)
On Error GoTo 0
If Rng Is Nothing Then
MsgBox "You cancelled"
Exit Sub
Else
myCol = Rng.Column
End If

' Insert blank rows at breaks
LastRow = Cells(65536, myCol).End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, myCol).Value <> Cells(i - 1, myCol).Value Then
Cells(i, myCol).EntireRow.Insert
End If
Next i

' Remove color from Row 2
Rows("2:2").Interior.ColorIndex = xlNone
End Sub

HTH,
Bernie
MS Excel MVP


"DKY" <DKY.1ul9yi_1125425148.1716@excelforum-nospam.com> wrote in message
news:DKY.1ul9yi_1125425148.1716@excelforum-nospam.com...
>
> Thanks Bernie, that works beautifully!
>
> Is there a way, or even a need to recode this and make it cleaner and
> maybe easier to understand? I kinda took from all kinds of snippets on
> the net.
>
>
> Code:
> --------------------
> Option Explicit
>
> Sub INSERT_SPACES_TXTBOX()
> Dim LastRow As Long
> Dim Rng As Range
> Dim strfirst As String
> Dim strletter As String
> Dim i As Long
> ' START SELECT COLUMN MSGBOX
> On Error Resume Next
> Set Rng = Application.InputBox("Select column with mouse", Type:=8)
> On Error GoTo 0
> If Rng Is Nothing Then
> MsgBox "You cancelled"
> Exit Sub
> End If
> ' END SELECT COLUMN MSGBOX
> ' START GET COLUMN LETTER
> strfirst = IIf(Chr$(64 + Rng.Column \ 26) = "@", "", Chr(64 + Rng.Column \ 26))
> strletter = strfirst & Chr$(64 + Rng.Column Mod 26)
> ' END GET COLUMN LETTER
> ' START SPACING
> LastRow = Range(strletter & "65536").End(xlUp).Row
> For i = LastRow To 2 Step -1
> If Range(strletter & i).Value <> Range(strletter & i - 1).Value Then
> Range(strletter & i).EntireRow.Insert
> End If
> Next
> ' END SPACING
> ' START REMOVE COLOR FROM ROW 2
> Rows("2:2").Select
> Range("A2").Activate
> Selection.Interior.ColorIndex = xlNone
> ' END REMOVE COLOR FROM ROW 2
> End Sub
>
> --------------------
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=385000
>