VBA code that will enter different values into difference cells.

[danbates]

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

 

[My Aswer Is This]

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'

 

[danbates]

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

 

[My Aswer Is This]

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"

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

 

[danbates]

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

 

[My Aswer Is This]

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:

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

 

[danbates]

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

 

[My Aswer Is This]

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