Option Box - Constant Updating

[bjarvis]

Hello,

Firstly want to apologise - after years of using formulas, I've only started using (or attempting) to use macros for the first time a few days ago, so my explanations may be a little rusty, but I'll do my best.

I'm creating a form in Excel, for relocations of a device.

The form has 4 Option Buttons (ActiveX Control) in 2 categories - 2 YES and 2 NO buttons in total.

For 1 of the categories, I'm asking the customer I'm asking the customer, if they require Same Day Delivery, with the date of collection asked earlier in the form.

If they state YES using the Option Button, Cell D24 populates the same date listed earlier in Cell D17 (for the collection). This works fine. However, if the date is then updated in Cell D17, the date/data in Cell D24 does not automatically update with these changes. You have to press the "NO" button to clear Cell D24, and then press the "YES" button, to repopulate the accurate information from D17.

The code used is:


<dl class="codebox" style="margin-right: 0px; margin-left: 0px; padding: 3px; border: 1px solid rgb(201, 210, 216); color: rgb(51, 51, 51); font-family: 'Lucida Grande', 'Trebuchet MS', Verdana, Helvetica, Arial, sans-serif; line-height: 18.2px;"><dt style="margin: 0px 0px 3px; padding: 0px; text-transform: uppercase; border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: rgb(204, 204, 204); font-size: 0.8em; font-weight: bold;">CODE: SELECT ALL</dt><dd style="margin: 0px; padding: 0px;"><code style="margin: 2px 0px; padding: 5px 0px 0px; overflow: auto; display: block; height: auto; max-height: 600px; font-stretch: normal; font-size: 0.9em; font-family: Monaco, 'Andale Mono', 'Courier New', Courier, mono; line-height: 1.3em; color: rgb(46, 139, 87);">Private Sub YES_Date_Click()
If Sheets("Mancourt Form").YES_Date.Value = True Then
Range("D24").Value = Range("D17").Value
End If
End Sub</code></dd></dl>

Any help would be greatly appreciated. Thank you.

 

[Alan_P]

Hi bjarvis,

This outta do the trick...

Right click on the sheet tab and select view code, paste the below;

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D17")) Is Nothing Then
    Exit Sub
ElseIf Sheets("Mancourt Form").YES_Date.Value = True Then
    Range("D24").Value = Range("D17").Value
End If

End Sub

Hope this helps,
Cheers,
Alan.

P.s. Welcome to the forum

 

[bjarvis]

Hi bjarvis,

This outta do the trick...

Right click on the sheet tab and select view code, paste the below;

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D17")) Is Nothing Then
    Exit Sub
ElseIf Sheets("Mancourt Form").YES_Date.Value = True Then
    Range("D24").Value = Range("D17").Value
End If

End Sub

Hope this helps,
Cheers,
Alan.

P.s. Welcome to the forum

Thanks Alan. This definitely helped to achieve a constantly updating cell. However has led to a bit of another problem.

So doing a test run of the form, upon entering the date into D17, automatically populates a date into D24 (which then changes upon changes to the original date).

However, then choosing the 'No' check box option (which correctly nulls the D24 cell, returning it to blank) and then re-choosing the 'Yes' option, the date in D24 does NOT generate. Even if I modify/delete and re-enter the date into Cell 17.

So you know what the Yes Check Box code looks like. This is the No.

Private Sub NO_Date_Click()   If Sheets("Mancourt Form").NO_Date.Value = True Then
   Range("D24").Value = Null
   End If
End Sub[code]

Thanks again for all your help and for the welcome. Really hoping to start diving into VBA and macros so appreciate the help while getting the learning curb.

 

[Alan_P]

Hmmm... I've not been able to replicate this on a test book

These are the 3 codes I've used and it seems to be working

Private Sub YES_Date_Click()
If YES_Date.Value = True Then
 Range("D24").Value = Range("D17").Value
End If
End Sub

Private Sub NO_Date_Click()
If NO_Date.Value = True Then
 Range("D24").Value = Null
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D17")) Is Nothing Then
    Exit Sub
ElseIf YES_Date.Value = True Then
    Range("D24").Value = Range("D17").Value
End If

End Sub

Maybe have a play round make sure there's no typos or anything like that.

If it still doesn't work, you could upload your file to DropBox or similar and I can take a look at it, might be something I can't see causing a problem...

 

[bjarvis]

Hmmm... I've not been able to replicate this on a test book

These are the 3 codes I've used and it seems to be working

Private Sub YES_Date_Click()
If YES_Date.Value = True Then
 Range("D24").Value = Range("D17").Value
End If
End Sub

Private Sub NO_Date_Click()
If NO_Date.Value = True Then
 Range("D24").Value = Null
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D17")) Is Nothing Then
    Exit Sub
ElseIf YES_Date.Value = True Then
    Range("D24").Value = Range("D17").Value
End If

End Sub

Maybe have a play round make sure there's no typos or anything like that.

If it still doesn't work, you could upload your file to DropBox or similar and I can take a look at it, might be something I can't see causing a problem...

Perfect Alan_P! Thanks very much for your assistance.

 

[NoSparks]

@ bjarvis

ever check your cross posts ?

 

[jessi81882]

Private Sub NO_Date_Click()   If Sheets("Mancourt Form").NO_Date.Value = True Then
   Range("D24").Value = Null
   End If
End Sub[code][/B][/U]


Try specifying where in the Mancourt sheet it should look for the date? So ...Sheets("Mancourt Farm").range("CELL").NO_Date.Value = True Then....