msg / input box

[Trevor3007]

good afternoon,

After several failures I still have an issue and hope some kind member can help?

i would like to generate a message upon opening the applicable file that would ask the user if they want to input data into another worksheet (within same file) & choose Y or N if yes , a input box would appear . The date would be in D2 , worksheet ref.
Many thanks in advance

KR
Trevor 3007

 

[Kamolga]

To have the macro to be launched when you open the workbook, go in VBA, 2x click "this workbook" in the left column below the sheets names and paste

Private Sub Workbook_Open()
If MsgBox("Input Data ?", vbYesNo + vbQuestion) = vbNo Then Exit Sub
Dim myValue As Variant
myValue = InputBox("Enter Date")
Do While myValue = "" Or Not IsDate(myValue)
        MsgBox ("You did not enter a date in the correct format!"), vbCritical, Error
        myValue = InputBox("Please Enter the Report Date with format dd/mm/yyyy", "Enter Date")
    Loop
Sheets("ref.").Range("D2").Value = myValue
End Sub

You can test the macro by clicking the play button in the bar or close and open the workbook. Always a good idea to save a copy before trying.

 

[Trevor3007]

To have the macro to be launched when you open the workbook, go in VBA, 2x click "this workbook" in the left column below the sheets names and paste

Private Sub Workbook_Open()
If MsgBox("Input Data ?", vbYesNo + vbQuestion) = vbNo Then Exit Sub
Dim myValue As Variant
myValue = InputBox("Enter Date")
Do While myValue = "" Or Not IsDate(myValue)
        MsgBox ("You did not enter a date in the correct format!"), vbCritical, Error
        myValue = InputBox("Please Enter the Report Date with format dd/mm/yyyy", "Enter Date")
    Loop
Sheets("ref.").Range("D2").Value = myValue
End Sub

You can test the macro by clicking the play button in the bar or close and open the workbook. Always a good idea to save a copy before trying.

Hi,

thank you for your reply,

Sorry I think I have mislead you?I need the user to input text NOT a date. sorry for the confusion, also the could you format the text to Proper case too?

Many thanks :}

 

[Trevor3007]

hi,

sorry my bad... cell ref should be D1 :/

hope you can sort?

 

[Kamolga]

hi,

sorry my bad... cell ref should be D1 :/

hope you can sort?

Simply change the last line (before "End sub") to

[COLOR=#333333][FONT=monospace][I]Sheets("ref.").Range("D1").Value = myValue[/I][/FONT][/COLOR]

 

[Kamolga]

Hi,

thank you for your reply,

Sorry I think I have mislead you?I need the user to input text NOT a date. sorry for the confusion, also the could you format the text to Proper case too?

Many thanks :}

Private Sub Workbook_Open()
If MsgBox("[COLOR=#ff8c00]Input Data ?[/COLOR]", vbYesNo + vbQuestion) = vbNo Then Exit Sub
Dim myValue As Variant
myValue = InputBox("[COLOR=#ff8c00]Enter text[/COLOR]")
Sheets("ref.").Range("D1").Value = Application.WorksheetFunction.Proper(myValue)
End Sub

 

[Trevor3007]

Private Sub Workbook_Open()
If MsgBox("[COLOR=#ff8c00]Input Data ?[/COLOR]", vbYesNo + vbQuestion) = vbNo Then Exit Sub
Dim myValue As Variant
myValue = InputBox("[COLOR=#ff8c00]Enter text[/COLOR]")
Sheets("ref.").Range("D1").Value = Application.WorksheetFunction.Proper(myValue)
End Sub

Good morning Kamolga,

sorry for not getting back to you ASAP & thank you for your your help.

Your code works great , but I must have had a bit of a DER day yesterday as I also forgot to include in my request that the applicable worksheet , the work tab would change to the same name as in D1...



Could you be a kind chap and sort? This would be the last request BTW

Many thanks in advance
Trevor3007

 

[Kamolga]

When you click sheet1(ref.) in VBA, you should have the properties below where you can change the name (the first one). If not, click the icon in the bar with a finger pointing a white rectangle.
Now change sheet1 (can be sheet2, 3 or whatever number) into Ref and then click this workbook and paste

[I][LEFT][COLOR=#333333][FONT=monospace]Private Sub Workbook_Open()
If MsgBox("[/FONT][/COLOR][COLOR=#ff8c00][COLOR=#ff8c00][FONT=monospace]Input Data ?[/FONT][/COLOR][/COLOR][COLOR=#333333][FONT=monospace]", vbYesNo + vbQuestion) = vbNo Then Exit Sub
[/FONT][/COLOR][/LEFT]
[/I][LEFT][I][COLOR=#333333][FONT=monospace]Dim myValue As Variant
myValue = InputBox("[/FONT][/COLOR][COLOR=#ff8c00][COLOR=#ff8c00][FONT=monospace]Enter text[/FONT][/COLOR][/COLOR][/I][COLOR=#333333][FONT=monospace][I]")
[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=monospace][I]Ref.Range("D1").Value = Application.WorksheetFunction.Proper(myValue)
Ref.name=[/I][COLOR=#333333][FONT=monospace][I]Application.WorksheetFunction.Proper(myValue)[/I][/FONT][/COLOR][I]
End Sub
[/I][/FONT][/COLOR][/LEFT]

Note that there are restriction in amount and special characters (I don't know exactly which). So you might get an error (Ref.name=

Application.WorksheetFunction.Proper(myValue) highlighted in yellow) if the inputbox is empty or does not meet sheet name requirement

Amso n

ote that changing the name in properties and use them in your code is a very good habbit in VBA, so that if someone changes the name of a sheet, your codes still works perfectly.​

<strike></strike>
​

 

[Trevor3007]

When you click sheet1(ref.) in VBA, you should have the properties below where you can change the name (the first one). If not, click the icon in the bar with a finger pointing a white rectangle.
Now change sheet1 (can be sheet2, 3 or whatever number) into Ref and then click this workbook and paste

[I][LEFT][COLOR=#333333][FONT=monospace]Private Sub Workbook_Open()
If MsgBox("[/FONT][/COLOR][COLOR=#ff8c00][COLOR=#ff8c00][FONT=monospace]Input Data ?[/FONT][/COLOR][/COLOR][COLOR=#333333][FONT=monospace]", vbYesNo + vbQuestion) = vbNo Then Exit Sub
[/FONT][/COLOR][/LEFT]
[/I][LEFT][I][COLOR=#333333][FONT=monospace]Dim myValue As Variant
myValue = InputBox("[/FONT][/COLOR][COLOR=#ff8c00][COLOR=#ff8c00][FONT=monospace]Enter text[/FONT][/COLOR][/COLOR][/I][COLOR=#333333][FONT=monospace][I]")
[/I][/FONT][/COLOR]
[COLOR=#333333][FONT=monospace][I]Ref.Range("D1").Value = Application.WorksheetFunction.Proper(myValue)
Ref.name=[/I][COLOR=#333333][FONT=monospace][I]Application.WorksheetFunction.Proper(myValue)[/I][/FONT][/COLOR][I]
End Sub
[/I][/FONT][/COLOR][/LEFT]

Note that there are restriction in amount and special characters (I don't know exactly which). So you might get an error (Ref.name=

Application.WorksheetFunction.Proper(myValue) highlighted in yellow) if the inputbox is empty or does not meet sheet name requirement

Amso n

ote that changing the name in properties and use them in your code is a very good habbit in VBA, so that if someone changes the name of a sheet, your codes still works perfectly.​

<strike></strike>
​

hi

so very grateful of you help. I pasted the code into 'this workbook' but it does not change the applicable tab name? it the 2nd sheet BTW :}

KR
Trevor 3007

 

[Trevor3007]

Hi,

this is the code use on another workbook:-

Private Sub Worksheet_Change(ByVal Target As Range)

''''''''''''''''''''''''''''''''''''''''''''


'Forces text to Proper case for the range d1


''''''''''''''''''''''''''''''''''''''''''''


If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub






    On Error Resume Next


    If Not Intersect(Target, Range("d1")) Is Nothing Then


        Application.EnableEvents = False


        Target = StrConv(Target, vbProperCase)


        Application.EnableEvents = True


    End If


    On Error GoTo 0




   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(False, False) = "D1" Then
      ActiveSheet.Name = Target.Value & "-data"
   End If






End Sub


its fine for that workbook, but I need the combo (msg/input box) as in your code.

Are you able to sort?

so sorry for any incon...hope your BH weekend is going OK?

KR
Trevor3007