edit offset

ndendrinos

Well-known Member
Joined
Jan 17, 2003
Messages
1,694
starting in column A this activates A:F on the same row.
Code:
Range(ActiveCell, ActiveCell.Offset(0, 5)).Activate
need to modify to activate ONLY columns A,D,E&F
 
Thank you Jim.
The clearing does not work (I expanded the selection a bit)
Code:
With ActiveCell
        .Offset(-1, -3).Range("A1").Range("A1,D1,E1,F1").Select   
    Selection.Clear contents
 End With

solved s/b ClearContents
 
Last edited:
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
You keep beating me to the solution(see my previous edit in #11.)
Nothing is easy the past hours & I encounter a new problem that needs I think a
Code:
Application.EnableEvents = False/...True

The whole code is like this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("A17:A35")) Is Nothing Then Target.Offset(0, 3).Select
  

If Not Intersect(Target, Range("D17").EntireColumn) Is Nothing Then
If WorksheetFunction.CountIf(Range("D:D"), Target) > 1 Then
MsgBox "This product has already been chosen above you might want to edit the quantity"
With ActiveCell
        .Offset(-1, -3).Range("A1").Range("A1,D1,E1,F1").Select
    
    Selection.ClearContents
    End With
End If
End If
Range("A35").End(xlUp)(2, 1).Activate

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Intersect(Target, Range("H2")) Is Nothing Then
       MsgBox "Do not edit this cell. Use this Form instead "
    End If
    
   If Not Intersect(Target, Range("B8")) Is Nothing Then
        UserForm2.Show
    End If
    'KKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKK
    If Not Intersect(Target, Range("D17:D35")) Is Nothing Then
    If Target.Value = "" Then UserForm1.Show
    
    End If
    
  
  If Not Intersect(Target, Range("H2")) Is Nothing Then
        UserForm4.Show
    End If
  
End Sub

The reason for this:
Code:
If Not Intersect(Target, Range("D17:D35")) Is Nothing Then
    If Target.Value = "" Then UserForm1.Show
    
    End If

is that the user may go to a previous row in column A and change the cell value WITHOUT having UserForm1 reappear.

hence I get an error saying that cannot show the form modally

Hope you can still spare some time with this post
Thank you
 
Upvote 0
Thanks have a good trip.
I've done some progress and now I've eliminated the error like this:
Code:
If Not Intersect(Target, Range("D17").EntireColumn) Is Nothing Then
If WorksheetFunction.CountIf(Range("D:D"), Target) > 1 Then
MsgBox "This product has already been chosen above you might want to edit the quantity"
With ActiveCell
        .Offset(-1, -3).Range("A1").Range("A1,D1,E1,F1").Select
    Application.EnableEvents = False
    Selection.ClearContents
    Application.EnableEvents = True
    End With
End If
End If

Range("A35").End(xlUp)(2, 1).Activate


End Sub

Now I have to adjust again the selection of the cells to be cleared
 
Upvote 0

Forum statistics

Threads
1,224,609
Messages
6,179,881
Members
452,948
Latest member
Dupuhini

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
Back
Top