Cell value to trigger VB code

[Earl Grey]

Morning all

I have been working on some code to force users of one of my workbooks to fill in information via a user form when they enter data in a cell.

The sheet in question is around 250 lines long with data entered into cells
g4 to g12(g13 is the total)
g14 to g22 and so on to cell 250

What im looking to do is trigger a userform to enter data in cell l4 when something is entered in g4.

And the same to happen with G5 and L5 and so on.

This is the code i have put together but i am unsure how to make in line specific without having hundreds of seperate peices of code.

Finally i would appreciate a little point in the right direction as to how i can write some code to delete the data entered into G* if the userform is closed without entering the shift.

Phew

Any advice would be appreciated

Earl

I have attached what i have done so far below

Sub Shiftdam1()

Shiftdam = InputBox(Prompt:="Enter Shift", _
Title:="Process Downtime Tracking", Default:="")
Range("l4").Value = Shiftdam
If Shiftdam = vbNullString Then Exit Sub
If IsNumeric(Shiftdam) Then
MsgBox "Sorry, text only"
TextBox1 = vbNullString
Cancel = True
End If


If CloseMode = 0 Then Cancel = True
End Sub

 

[Andrew Poulsom]

How are you calling that procedure?

If data is entered by the user, you could use the Worksheet_Change event procedure to monitor changes in the relevant cells in column G and prompt for an entry in the same row in column L.

 

[Earl Grey]

Thats the bit im struggling with is how to work the change event i have some code in already which disables print and Cut Copy paste for the majority of users.

Im wondering if this is causing my problems this is how i turn the above back on for certain users

CurrentUserName = Environ("Username")
If CurrentUserName = "mattsmith" Then
Application.EnableEvents = False
EnableAllClear
End If

CurrentUserName = Environ("Username")
If CurrentUserName = "andhasty" Then
Application.EnableEvents = False
EnableAllClear
End If


CurrentUserName = Environ("Username")
If CurrentUserName = "sdowbekin" Then

Application.EnableEvents = False
EnableAllClear
End If

CurrentUserName = Environ("Username")
If CurrentUserName = "dadams" Then
Application.EnableEvents = False
EnableAllClear
End If

 

[Andrew Poulsom]

You haven't told me how you are calling the Shiftdam1 procedure.

Please post your existing Worksheet_Change event procedure.

 

[Earl Grey]

Thats the crux of the problem ive no idea how to start a simple change procedure to be able to work out a complete solution.

Just need a little push in the right direction

 

[Andrew Poulsom]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Shiftdam As Variant
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 7 Then Exit Sub
    If Target.Row < 4 Or Target.Row > 250 Then Exit Sub
    Do
        Shiftdam = InputBox(Prompt:="Enter Shift", Title:="Process Downtime Tracking", Default:="")
        If Shiftdam = "" Then
            Application.EnableEvents = False
            Target.ClearContents
            Application.EnableEvents = True
            Exit Sub
        ElseIf IsNumeric(Shiftdam) Then
            MsgBox "Sorry, text only"
        Else
            Target.Offset(0, 5).Value = Shiftdam
            Exit Sub
        End If
    Loop
End Sub

To do so, right click the sheet tab and choose View Code. Paste the code into the window on the right. Press Alt+F11 to return to your worksheet and enter something in a cell in column G.

 

[Earl Grey]

Many thanks for your help.

Im now beginning to understand how it works

Earl