Cell value to trigger VB code

Earl Grey

New Member
Joined
Jan 17, 2006
Messages
15
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jan 17, 2006
Messages
15
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You haven't told me how you are calling the Shiftdam1 procedure.

Please post your existing Worksheet_Change event procedure.
 

Earl Grey

New Member
Joined
Jan 17, 2006
Messages
15

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try this:

Code:
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.
 

Forum statistics

Threads
1,136,369
Messages
5,675,359
Members
419,565
Latest member
Phil57

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