input box

[Trevor3007]

hi,

i need to create a input box so when the user puts the text into, it will place the text in d1 on sheet 2

I am able to do the above only on sheet 1 . Can anyone help?

KR
Trevor3007

 

[Joe4]

Whatever code you have, you probably have it placing it in the cell like this:

Range("D1")...

where your range does not have a sheet reference.

If you want it to put it on Sheet, add the sheet reference to your range, i.e.

Sheets("Sheet2").Range("D1")...

If you are having issues instituting this, please post your code.

 

[Kenneth Hobson]

There are many ways depending on what you want. e.g. Sheet objects like data validation dropbox, textbox controls (ActiveX or Form), Inputbox in a Userform, VBA's Application.Inputbox, VBA's InputBox, etc. Here is the latter.

Sub DoIt()
  Worksheets("sheet 2").Range("D1").Value = _
    InputBox("Enter a value for 'sheet 2!D1'.", "Data Entry")
End Sub

 

[Trevor3007]

Whatever code you have, you probably have it placing it in the cell like this:

Range("D1")...

where your range does not have a sheet reference.

If you want it to put it on Sheet, add the sheet reference to your range, i.e.

Sheets("Sheet2").Range("D1")...

If you are having issues instituting this, please post your code.

Hi joe4,

this is what I have:

Sub Test_Function()
Dim val As String
val = InputBox("Enter Applicable Text")
Sheets("Sheet 2").Range ("D1")
End Sub

it don't work BTW :{

KR
Trevor3007

 

[Kenneth Hobson]

Sheets("Sheet 2").Range ("D1").Value = InputBox("Enter Applicable Text")

 

[Joe4]

You never set your range equal to your Input Box.
To edit your original code, it may look something like this:

[COLOR=#333333]Sub Test_Function()[/COLOR]
[COLOR=#333333]Dim val As String[/COLOR]
[COLOR=#333333]val = InputBox("Enter Applicable Text")[/COLOR]
[COLOR=#333333]Sheets("Sheet 2").Range("D1")[/COLOR][COLOR=#ff0000] = val[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[Trevor3007]

Whatever code you have, you probably have it placing it in the cell like this:

Range("D1")...

where your range does not have a sheet reference.

If you want it to put it on Sheet, add the sheet reference to your range, i.e.

Sheets("Sheet2").Range("D1")...

If you are having issues instituting this, please post your code.

H Joe,

sorry sent you wrong code:


Currently use this:-

Private Sub Workbook_Open()
Dim myValue As Variant
Dim ws As Worksheet

If MsgBox("Do You Need To Insert Name ?", 36, "Insert Name") = vbNo Then Exit Sub

'Sheet3 is sheets code name DO NOT CHANGE
Set ws = Sheet3

Do
'show inputbox
myValue = InputBox("Enter New Name", "Enter Tab Name")
'cancel pressed
If StrPtr(myValue) = 0 Then Exit Sub
'loop until tab name entered
Loop Until Len(myValue) > 0

'change to proper case
myValue = Application.WorksheetFunction.Proper(myValue)

'update sheet
With ws
'add name to cell
.Range("D1").Value = myValue
'change tab name
.Name = CStr(myValue) & "-Codes"
End With

End Sub

what I need as the above is evoked upon opening the file, but should the user 'mis type' then they cannot rename . So I thought if create a button they can alter accrordinlgy?

KR
Trevor

 

[Joe4]

So I thought if create a button they can alter accrordinlgy?

So, is that what the other code is?
Did you see the issues I pointed out with that code, and the suggestions Kenneth and I made?

 

[Trevor3007]

Sheets("Sheet 2").Range ("D1").Value = InputBox("Enter Applicable Text")

thank you . very much appreciated:}

 

[Trevor3007]

So, is that what the other code is?
Did you see the issues I pointed out with that code, and the suggestions Kenneth and I made?

hi,

yes I did but am confused. com & dont have a scobie ...sorry.

I am good at comming up ithe the 'interace/look' of but the back end is such a datk place when the VB required is out of 'recoerd macro' and my very limited knowledge thereof.

Hence why I sort mr excel & co for much needed assigtance.

KR
Trevor3007