Select column that matches a value entered in a User Form TextBox

[Gwyzor]

I am building an Excel 2007 User Form in VB. I want my users to be able to
type the day of month (i.e. 6 for the 6th of the month) and have code that
automatically selects the column that is set up for that date to be used.
Each column has the day of the month as the top field of the column.
Therefore, if they type '6', I want to select the equal value, '6' in the day
column, and use that column. Have tried several functions, and am not coming
up with the correct code to accomplish this.

 

[Robert Flanagan]

I assume you meant "6" inthe day row, not column. Assuming I'm right, and
few other assumptions, then

dim C as integer
C = userform1.textbox1.text
if c = 0 then
msgbox "enter a number"
exit sub
end if
'assuming your entries are in row 1, and start in column B, since column A
is likely to be a title column, then:
Cells(1,C+1).select

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Rick Rothstein]

Something like this maybe...

Sub AutoFitRowHeightWhenNecessary()
Dim R As Range
Dim CurrentHeight As Double
Const MinRowHeight As Double = 51
On Error GoTo Whoops
Application.ScreenUpdating = False
For Each R In Worksheets("Sheet1").UsedRange.Rows
R.AutoFit
If R.RowHeight < MinRowHeight Then
R.RowHeight = MinRowHeight
End If
Next
Whoops:
Application.ScreenUpdating = True
End Sub

 

[fisch4bill]

Hi Gwyzor,

The following code may get you on the right track. It is a simple macro that
asks the user to specify the day of the month, and then activates the cell in
the appropriate column just below the last used cell. With this particular
code, you don't even have to have header values that correspond to the days
of the months. Regardless of what's in the first row, this code will select
the appropriate column:

'code starts:

Sub SelectColumn()
Dim DayNum As Integer
DayNum = InputBox("Please input today's" & vbNewLine & "day of the month")
Cells(Cells(Rows.Count, DayNum).End(xlUp).Row + 1, DayNum).Activate
End Sub

'code ends

HTH

 

[Rick Rothstein]

Sorry... it looks like I posted my response to the wrong message thread.