entering data

Emm

Board Regular
Joined
Nov 29, 2004
Messages
165
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
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
 

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,079
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. MacOS
you created a shortcut key for material 1.

do you have the code? if so post it
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
Code:
Private Sub CommandButton1_Click()
Dim myNum As Long
myNum = Application.InputBox("Enter Number", type:=1)
ActiveCell.Value = "Material " & myNum
End Sub
 

Emm

Board Regular
Joined
Nov 29, 2004
Messages
165
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

Board Regular
Joined
Apr 2, 2006
Messages
122
or
Code:
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

Board Regular
Joined
Nov 29, 2004
Messages
165
Thanks Blackbox.....

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

Thanks all ..
 

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
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:
Code:
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
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,785
Latest member
KingGideon

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
Top