VBA code that will enter different values into difference cells.

danbates

Active Member
Joined
Oct 8, 2017
Messages
377
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I was wondering if someone could help me please?

When a number in column A is entered, I would like the following to happen:

The number 11 automatically enters in column C and 800 enters into column E.

With a value in column A. Once column B is selected I'd like it to call for a userform called 'Options'

If column A is deleted then column C & E deletes as well.

If column B is deleted, it deletes column D as well.

So columns A & B are independent from each other.
This is the part I can't figure out.

Any help would be appreciated.

Regards

Dan
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Entering the values like you want can be done.
But as far as I know there is no AutoSheet event script that will delete a column if you delete a column.

And what does this mean:
Once column B is selected I'd like it to call for a userform called 'Options'
 
Last edited:
Upvote 0
Hi,

Sorry I've just realised my question is quite vague.

When I said about deleting column C & D when A is deleted, I meant just the contents. Same for B & D.

The 'Options' is a userform I have ready to use. It's the code I'm struggling with to get it all working together.

The userform will fill out column B & D.

HTH
 
Upvote 0
Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Still not sure what you want this means:
"With a value in column A. Once column B is selected I'd like it to call for a userform called 'Options'"
Do you mean you want to call a macro to run? If so what macro?
And this just happens any time you select any cell in column "B" if there is a value in Column "A"


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Value <> "" Then Target.Offset(0, 2).Value = "11": Target.Offset(0, 4).Value = "800"
If Target.Column = 1 And Target.Value = "" Then Target.Offset(0, 2).Value = "": Target.Offset(0, 4).Value = ""
End Sub
 
Upvote 0
Hi,

Your code works perfectly so thank you for that.

I have a userform called 'Options' and I only want it to be triggered when cell A has a value.
So if cell A is empty and someone moves onto cell B the macro doesn't trigger.
The macro is called 'OpenOptions'
If I could have it so it didn't trigger if cell A & B had a value then great.
The worksheet will be filled out 1 row at a time.

HTH
 
Upvote 0
So anytime a value in entered into column "A" column "C" will have "11" entered Column "G" will have "800" entered and the sub routine named "OpenOptions will be activated.
There is no need to move to column "B"
Try this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Value <> "" Then Target.Offset(0, 2).Value = "11": Target.Offset(0, 4).Value = "800": Call OpenOptions
If Target.Column = 1 And Target.Value = "" Then Target.Offset(0, 2).Value = "": Target.Offset(0, 4).Value = ""
End Sub
 
Upvote 0
Hi,

Yes, it can work like that thinking about it and I think that could be better solution.

I've tried it and it all works just fine with the macro, so thank you so much for your help.

Kind Regards

Dan
 
Upvote 0
Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.
Hi,

Yes, it can work like that thinking about it and I think that could be better solution.

I've tried it and it all works just fine with the macro, so thank you so much for your help.

Kind Regards

Dan
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

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