I need a text box and can't find one
 

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

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=38500

I need a text box and can't find one
 

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" wrote in message
...

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

I need a text box and can't find one
 

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
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=385000

I need a text box and can't find one
 

::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

I need a text box and can't find one
 

Hi DKY,

Perhaps:

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

---
Regards,
Norman



"DKY" wrote in message
...

::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

I need a text box and can't find one
 

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

I need a text box and can't find one
 

Change
Dim LastRow As Range
to
Dim LastRow As Long

HTH,
Bernie
MS Excel MVP


"DKY" wrote in message
...

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

I need a text box and can't find one
 

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

I need a text box and can't find one
 

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" wrote in message
...

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