entering data

[Emm]

Hi all...

I have a problem... i would like to enter the words "Material" into a selected range with a 1,2,3,4, or 5 after it .. i.e. "Material 2"

I have setup a shortcut key to enter "Material 1 ... but was thinking rather than having 5 shortcut keys .. if i had a box pop up and ask for a number... so that it added the word Material to the selected number....


any help greatly appreciated

keith

 

[goldfish]

You could format the cell as
"Material "0
That way the cell would appear to have "Material 1" in it but all you would have to do is type 1.

HTH,
~Gold Fish

 

[Emm]

No good ... I formulate off these entries

 

[QuietRiot]

you created a shortcut key for material 1.

do you have the code? if so post it

 

[jindon]

Hi all...

I have a problem... i would like to enter the words "Material" into a selected range with a 1,2,3,4, or 5 after it .. i.e. "Material 2"

I have setup a shortcut key to enter "Material 1 ... but was thinking rather than having 5 shortcut keys .. if i had a box pop up and ask for a number... so that it added the word Material to the selected number....


any help greatly appreciated

keith

try

Private Sub CommandButton1_Click()
Dim myNum As Long
myNum = Application.InputBox("Enter Number", type:=1)
ActiveCell.Value = "Material " & myNum
End Sub

 

[Emm]

OK figured it out ...


Dim MatNo As String
Dim MatInput As String

MatInput = InputBox("Enter Material Number?")
MatNo = "MATERIAL" & " " & MatInput
Selection.FormulaR1C1 = MatNo

End Sub

Thanks Jindon ... just found yours when I replied...

Keith

 

[blackbox]

or

Sub EnterNumber()
Dim X As Integer
X = InputBox(prompt:="Please enter a number between 1 and 5", _
          Title:="Material Count", Default:="")
          
      If X >= 1 Then
        If X <= 5 Then
         Range("A1") = "Material" & " " & X
         Else
       WrongInput
   End If
   End If
End Sub

Sub WrongInput()
MsgBox "You can only enter a number between 1 and 5"
EnterNumber
End Sub

assign a hotkey to Sub EnterNumber

 

[Emm]

Thanks Blackbox.....

thats great ... a slightly changed it to enter as per selection..

Thanks all ..

 

[goldfish]

If you have a particular range you want this to occur in, an easier way would be to put the following code in that worksheet, and set the define name as instructed:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
     'note that this implies that in Excel you did Insert | Name | Define...
     'and defined a named range «AddMaterial» for those cells
     'that you want Add Material
    Const c_strRangeName As String = "AddMaterial"
    Dim rngCell As Range

    If Intersect(Target, Range(c_strRangeName)) Is Nothing Then Exit Sub

    Application.EnableEvents = False
    For Each rngCell In Intersect(Target, Range(c_strRangeName)).Cells
        If rngCell.Value = 1 Or rngCell.Value = 2 Or _
                rngCell.Value = 3 Or rngCell.Value = 4 Or _
                rngCell.Value = 5 Then
            rngCell.Value = "Material " & rngCell.Value
        End If
    Next rngCell
    Application.EnableEvents = True
End Sub

Then just type 1, 2,..., or 5 into a cell and it will automatically change (the actual cell value will change unlike my format example before)

~Gold Fish