1

I have been looking around to find good examples, but can't find what I need.
Here is the context: The code is for a sales tracker worksheet with around 50 vendors (each of them can add value and most of them didn't know anything about Excel).

I want to select the first empty cell (where the first they can enter a value is B5, not higher, because the top of the sheet includes some instructions). In fact, from this cell (Date value is in Column B, and begin in Row 5) the second date value is in B6

Add the Date (date or now) as activecell.value
Then 2 cells to the right activecell.offset(0,2)
And insert the value of the textbox (their ID).

For now, I can add the date and the Textbox ID.
Here what I have so far:

Sub CommandButton1_click()

Dim Input_ID As String, Date_in As String

Date_in = Format(Now, "DD-MMM")
ActiveCell.Value = Date_in
Input_ID = InputBox("SVP entré votre ID ", "Data Entry Form")
ActiveCell.Offset(0, 2) = Input_ID

End Sub

But is it possible to make that command/button only available for column "B?" Because I don't what them add a date and their ID to another Column.
PS: I More or less begin in VBA, I learn from a bit of everywhere, So if you could add some explanation in your code, i appreciate it. Thanks

Edit1: Post from comment

Sub Date_insert_click() 

Dim Input_ID As String, Date_in As String 
Dim ws As Worksheet 
Set ws = ActiveSheet 'change to your actual worksheet 
'Dim Date_in As Date 
Date_in = Format(Now, "DD-MMM")
With ws.Range("B" & ws.Rows.Count).End(xlUp) 
    If .Row >= 4 Then .Offset(1, 0).Value = Date_in Else Exit Sub 
    Input_ID = InputBox("SVP entré votre ID ", "Data Entry Form") 
    If Input_ID <> "" Then .Offset(1, 1).Value = Input_ID Else .Offset(1, 0).Value = ""
End With 

End Sub 

But I found a weakness. If I select a cell anywhere down like K378,
I Still can add the value (date_In or value of the inputbox) but can't see it because the cell isn't active.

L42
  • 19,427
  • 11
  • 44
  • 68
Peter
  • 21
  • 3

1 Answers1

0

Try this as commented:

Sub CommandButton1_click()

Dim Input_ID As String, Date_in As String
Dim ws As Worksheet

Set ws = Thisworkbook.Sheets("Sheet1") 'change to your actual worksheet
Date_in = Format(Now, "DD-MMM")

With ws.Range("B" & ws.Rows.Count).End(xlUp)
    If .Row >= 4 Then .Offset(1, 0).Value = Date_in Else Exit Sub
    Input_ID = InputBox("SVP entré votre ID ", "Data Entry Form")
    If Input_ID <> "" Then .Offset(1, 2).Value = Input_ID Else .Offset(1, 0).Value = ""
End With

End Sub

Edit1: Explanation as requested

Q: Why pass Worksheet object to a variable?
A: HERE is some explantion for this question. Also it makes your code a lot more readable and easy to debug.

Explaining the code:

'This line simply finds the last cell in Column B
With ws.Range("B" & ws.Rows.Count).End(xlUp)         
    'other code here
End With

Why use With? I used with because all the coding focuses on Column B and other data input is also reference to it.
You can also see explanation on the advantages of using it in the link I provided above.

With ws.Range("B" & ws.Rows.Count).End(xlUp)
    'Since we used With, you can directly access the Range properties
    'The following line uses the Row and Offset property
    'Row returns the row number of the range you're workning on
    'Offset literally offets the range you are currently working on
    If .Row >= 4 Then .Offset(1, 0).Value = Date_in Else Exit Sub
    'This next line is already known to you, no need to explain
    Input_ID = InputBox("SVP entré votre ID ", "Data Entry Form")
    'Next line checks if Input_ID is supplied
    'If yes, we use offset to get to the 2nd column from the current range
    'If no, we delete the Date_In value
    If Input_ID <> "" Then .Offset(1, 2).Value = Input_ID Else .Offset(1, 0).Value = ""
End With

I hope I explained it enough.
But if ever you still need more explanation, just comment it out.
If you encounter dificulties somewhere just post another question.

Community
  • 1
  • 1
L42
  • 19,427
  • 11
  • 44
  • 68
  • Hi, I tested it, seem to work But, i appreciate some explanation : What the use of the ligne "Set Ws .... " I know the "Ws" is for Worksheet, but can we not use it/ add it in the code ?. The same command is used in many other worksheet, and I appreciate if I can avoid to ajust the ".sheets("sheet1") part. Thanks – Peter Mar 17 '14 at 21:40
  • Hi, After some test, i modify to this : Sub Date_insert_click() Dim Input_ID As String, Date_in As String Dim ws As Worksheet Set ws = ActiveSheet 'change to your actual worksheet 'Dim Date_in As Date Date_in = Format(Now, "DD-MMM") With ws.Range("B" & ws.Rows.Count).End(xlUp) If .Row >= 4 Then .Offset(1, 0).Value = Date_in Else Exit Sub Input_ID = InputBox("SVP entré votre ID ", "Data Entry Form") If Input_ID <> "" Then .Offset(1, 1).Value = Input_ID Else .Offset(1, 0).Value = "" End With End Sub – Peter Mar 17 '14 at 22:51
  • Hi, After some test, i modify to this : Sub Date_insert_click() Dim Input_ID As String, Date_in As String Dim ws As Worksheet Set ws = ActiveSheet 'change to your actual worksheet 'Dim Date_in As Date Date_in = Format(Now, "DD-MMM") With ws.Range("B" & ws.Rows.Count).End(xlUp) If .Row >= 4 Then .Offset(1, 0).Value = Date_in Else Exit Sub Input_ID = InputBox("SVP entré votre ID ", "Data Entry Form") If Input_ID <> "" Then .Offset(1, 1).Value = Input_ID Else .Offset(1, 0).Value = "" End With End Sub But, I found a weakness, if I select a cell anywhere down (like K378) – Peter Mar 17 '14 at 22:57
  • , I Still can add the value (date_In or value of the inputbox) but Can't see it because the cell isn't active. So – Peter Mar 17 '14 at 22:57