Question about dates.

[superbeast326]

I have two columns, A and B. Both have 50 rows. Column A is filled entirely with "No". What I want is that the moment I switch a row in Column A to "Yes". I want the row in Column B to store the date that the change was made. Is this possible?

 

[superbeast326]

By the way, are variable names case-sensitive?

 

[Peter_SSs]

Looks reasonable, give it a try.

 

[Gerald Higgins]

...are variable names case-sensitive?

Off the top of my head, I can't remember, but it should be fairly easy for you to test this.

 

[superbeast326]

Here is another question,

How do you declare a range as a public variable?

 

[superbeast326]

Is it possible to replace a certain part of code, i.e the range of Rng2, with the range D51:E52 using a code?

 

[superbeast326]

Peter:

I think I have an idea for using the DV list code in a class module. I am going to put the code in a Class method. I am going to put it in a class method. I am going to call the module CDVList and the method, GenerateDV. My question is, will the following code work.

Public Function GenerateDV(Range1 As String, Range2 As String)

[FONT=Courier]  [COLOR=#00007F]Dim[/COLOR] Rng1 [COLOR=#00007F]As[/COLOR] Range, Rng2 [COLOR=#00007F]As[/COLOR] Range, c [COLOR=#00007F]As[/COLOR] Range
    [COLOR=#00007F]Dim[/COLOR] rowdiff [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Long[/COLOR], coldiff [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Long[/COLOR]
    [COLOR=#00007F]Dim[/COLOR] DVvals [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]String[/COLOR]
    
    [COLOR=#00007F]Set[/COLOR] Rng1 = Range(Range1)
    [COLOR=#00007F]Set[/COLOR] Rng2 = Range(Range2)
    [COLOR=#00007F]If[/COLOR] Rng1.Rows.Count = Rng2.Rows.Count And _
            Rng1.Columns.Count = Rng2.Columns.Count [COLOR=#00007F]Then[/COLOR]
        rowdiff = Rng1.Row - Rng2.Row
        coldiff = Rng1.Column - Rng2.Column
        Rng2.Validation.Delete
        [COLOR=#00007F]For[/COLOR] [COLOR=#00007F]Each[/COLOR] c [COLOR=#00007F]In[/COLOR] Rng2.Cells
            DVvals = c.Offset(rowdiff, coldiff).Value
            DVvals = Replace(DVvals, " vs ", ",", 1, -1, vbTextCompare)
            [COLOR=#00007F]If[/COLOR] Len(DVvals) > 0 and c.value = 0 [COLOR=#00007F]Then[/COLOR]
                [COLOR=#00007F]With[/COLOR] c.Validation
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                        Operator:=xlBetween, Formula1:=DVvals
                    .IgnoreBlank = [COLOR=#00007F]True[/COLOR]
                    .InCellDropdown = [COLOR=#00007F]True[/COLOR]
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = [COLOR=#00007F]True[/COLOR]
                    .ShowError = [COLOR=#00007F]True[/COLOR]
                [COLOR=#00007F]End[/COLOR] [COLOR=#00007F]With[/COLOR]
            [COLOR=#00007F]End[/COLOR] [COLOR=#00007F]If[/COLOR]
        [COLOR=#00007F]Next[/COLOR] c
    [COLOR=#00007F]End[/COLOR] [COLOR=#00007F]If[/COLOR]
[COLOR=#00007F]End[/COLOR] [COLOR=#00007F]Sub[/COLOR][/FONT]

Also, when should I use Application.EnableEvents?

 

[superbeast326]

Could you please, kindly, help me for one last time?

 

[Peter_SSs]

My question is, will the following code work.

I am not strong in Class Modules, but in any case my answer to that question would be: "Try it and see for yourself".

Also, when should I use Application.EnableEvents?

That question is a bit like "How long a piece of string should I use?"

You set EnableEvents to False whenever you don't want an event to subsequently trigger its event code.
You set EnableEvents to True whenever you do want an event to subsequently trigger its event code.

 

[superbeast326]

Peter:

Sorry to trouble you again but I got a question.

I have 4 pair of numbers:
1. 7,8
2. 5,6
3. 4,3
4. 2,1

What I want is that, if a column has a number, I want the adjacent column to display the other number in the pair. So If A1 has 8, I want B1 to have 7 and vice versa and so on and so forth. Is this possible

 

[Peter_SSs]

1. Are they all single digit numbers?
2. Are the number pairs stored anywhere on the sheet? If so, where?
3. If the pairs are stored, how do we know which pair to apply to a particular cell?

If they are single digit numbers, and the numbers are not stored anywhere, then try this in B1. It assumes no number other than 7 or 8 would be entered in A1.

=IF(N(A1),SUBSTITUTE(78,A1,"",1)+0,"")