How to insert a text field into appropriate worksheet based on alphabetical value?

genepaton

New Member
Joined
Jun 1, 2011
Messages
37
Hi Guys,

Im trying to make a simple macro for cataloging some equipment and where its located.
I've created a user form where there is a free text field for Equipment. Instead of just inserting this entry into the first available cell, i would like for it to place it in the first available cell under the corresponding first letter of the equipment to the named worksheets A-Z.

(sorry if that isn't too clear)

I have named the worksheets A through to Z, and upon entering the free text 'Equipment' name, say for instance Trolley, for it to recognise the T, open the worksheet named T and insert the equipment name and location etc across the first free column.

Any help greatly appreciated.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
I've attached what i've reversed engineered so far, but i don't know if im on the right track.

The following is supposed to place the entry into worksheet A and then moves it the appropriate sheet (but it aint working)

In the Workbook Module

Code:
Sub Button2_Click()
UserForm1.Show
End Sub

Sub MoveToNewSheet(ByVal strSrceSheet As String, ByVal lngRow As Long, _
    ByVal strStatus As String)
     
    Select Case strStatus
    Case "A*": strDestSheet = "A"
    Case "B*": strDestSheet = "B"
    Case "C*": strDestSheet = "C"
    Case "D*": strDestSheet = "D"
    Case "E*": strDestSheet = "E"
    Case "F*": strDestSheet = "F"
    Case "G*": strDestSheet = "G"
    Case "H*": strDestSheet = "H"
    Case "I*": strDestSheet = "I"
    Case "J*": strDestSheet = "J"
    Case "K*": strDestSheet = "K"
    Case "L*": strDestSheet = "L"
    Case "M*": strDestSheet = "M"
    Case "N*": strDestSheet = "N"
    Case "O*": strDestSheet = "O"
    Case "P*": strDestSheet = "P"
    Case "Q*": strDestSheet = "Q"
    Case "R*": strDestSheet = "R"
    Case "S*": strDestSheet = "S"
    Case "T*": strDestSheet = "T"
    Case "U*": strDestSheet = "U"
    Case "V*": strDestSheet = "V"
    Case "W*": strDestSheet = "W"
    Case "X*": strDestSheet = "X"
    Case "Y*": strDestSheet = "Y"
    Case "Z*": strDestSheet = "Z"
         
    End Select
     
    With Worksheets(strDestSheet)
        Worksheets(strSrceSheet).Range("A" & lngRow & ":G" & lngRow).Copy _
        Destination:=.Range("A" & .Range("A65536").End(xlUp).Row + 1)
    End With
    Worksheets(strSrceSheet).Rows(lngRow).Delete
End Sub

On each subsequent worksheets code

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strDestSheet As String
    If Target.Column <> 1 Then Exit Sub
     
    MoveToNewSheet ActiveSheet.Name, Target.Row, Target.Value
End Sub

And the userform code

Code:
Private Sub Cancel_Click()
    Unload Me
End Sub

Private Sub Clear_Click()
    Call UserForm_Initialize
End Sub

Private Sub OK_Click()
    ActiveWorkbook.Sheets("A").Activate
    Range("A2").Select
    Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(1, 0).Select
    End If
    Loop Until IsEmpty(ActiveCell) = True
    ActiveCell.Value = TextBox1.Value
    ActiveCell.Offset(0, 1) = TextBox2.Value
    ActiveCell.Offset(0, 2) = TextBox3.Value
    Unload Me
End Sub


Private Sub UserForm_Initialize()
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox1.SetFocus
End Sub
 
Upvote 0
Select case doen't recognize wild cards, one has to incorporate Like into it for that.
Try
Code:
strDestSheet = UCase(Left(strStatus, 1))

or
Code:
Private Sub OK_Click()
    On Error Goto ErrorOut
    With ActiveWorkbook.Sheets(UCase(Left(TextBox1.Text, 1)))
        With .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
            .Value = TextBox1.Text
            .Offset(0, 1).Value = TextBox2.Text
            .Offset(0, 2).Value = TextBox3.Text
        End With
    End With
    Unload Me
ErrorOut:
    On Error Goto 0
End Sub
 
Last edited:
Upvote 0
Hi Mikerickson,

Thank you for your post and pointing out about cross posts to me. I didn't realise but the explanation makes some good points. I'll be sure to include links in future.

Your code works brilliant and i didn't even need all those Select cases, i just used the Command OK click that you suggested.

Thanks for your help!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top