Alteration of code

[hallingh]

I have a worksheet where when a user selects cell F6 a userform pops up prompting them for a month and year. Once they select it, it adds the numbers in the cells to the left and puts them in the correct cell based on a heading in the form of "Jan-11" or "Dec-10" etc. I need to alter it slightly. This is my code on the worksheet:

Option Explicit
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
  If Target.Address = "$F$6" Then
    EnterDate.Show
  End If
End Sub

Pretty simple obviously. The code in my userform is a bit more complicated:

Private Sub OKSubmit_Click()
Dim myNum As Long
EnterDate.Hide
myNum = ActiveSheet.Range("E6").Value + ActiveSheet.Range("E7").Value + ActiveSheet.Range("E8").Value + ActiveSheet.Range("E9").Value + ActiveSheet.Range("E10").Value + ActiveSheet.Range("E11").Value
Dim LCol As Long
Dim rsp As String
Dim y As Long
Const lngHeaderRow = 12
On Error GoTo EndSub
    LCol = Cells(lngHeaderRow, 2).End(xlToRight).Column
    rsp = Me.MonthBox.Value & "-" & Right(Me.YearBox.Value, 2)
    For y = ActiveSheet.Cells(lngHeaderRow, 2).End(xlToRight).Column To 1 Step -1
        If ActiveSheet.Cells(lngHeaderRow, y).Text = rsp Then
            ActiveSheet.Cells(13, y).Value = myNum
        End If
    Next y
EndSub:
Me.MonthBox.Value = ""
Me.YearBox.Value = ""
 

End Sub

The way the sheet is set up is there are 6 cells available for the user to enter numbers into, E6:E11. There is only one cell that brings up the userform, which then adds the 6 cells together and sets the correct cell equal to the sum. I need to have each cell to the right of E6:E11 bring up the userform, but instead of adding together all the values, it needs to take the value from the cell directly to the left of it and put that in the same cell the code has it doing now, but it also needs to add it to the cell directly to the left of that cell. If I am being unclear, please let me know.

A couple ideas I have:

I don't really want to build 5 more userforms that all do the same thing, but each one needs to take the value from a different row (the first takes it from E6, the second from E7, third from E8, etc.) Could I set a variable in my worksheet code and give it a value depending on what cell is clicked? Some thing like this:

Option Explicit
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
Dim CellNum As Long
 
  If Target.Address = "$F$6" Then
    CellNum = 6
    EnterDate.Show

  End If
 
   If Target.Address = "$F$7" Then
    CellNum = 7
    EnterDate.Show
 
 
  ............
End Sub

If I did this, could I carry that value over into my userform code?

Any suggestions would obviously be GREATLY appreciated. I'm close (I think) to being able to do this, but I can't quite figure it out. Thanks very much for the looks and any help you can provide.

Hank

 

[Rasm]

is 'EnterDate' the name of your userform? - I will look at this tomorrow - it also looks like you have two textboxes on the userform - is that right?

If you are picking a date - why not use the calendar control?


Give me details of the names of the objects on the userform.

 

[Rasm]

This code illustrates how you can load the userform when either F6 to F11 is selected - the userform then reads which cell coordinates is selected into the label - using the ActiveCell property Row & Col.

I think that is what the esssence of the question is - hope that is right - so fit that concept to your code

Sheet code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Select Case Target.Address
        Case "$F$6", "$F$7", "$F$8", "$F$9", "$F$10", "$F$11"
            Load FormEnterdate
            FormEnterdate.Show
    End Select
End Sub

FormEnterDate userform code

Private Sub UserForm_Activate()
   Label1.Caption = "Row selected = " & ActiveCell.Row & "   Column selected = " & ActiveCell.Row
End Sub