VBA HELP One user form, 3 boxes, send to 3 cells

[Diabentes]

Hello

I’m wondering if anyone can help. I’m a bit of a VBA novice to say the least. I originally had one box from a user form posting into one cell when only 1 column was being checked. That worked fine but I want to improve it. My problem is as follows.

I have created a user form. When anyone tries to edit column 2, 3, or 4, I want the user form to appear.

The user form has 2 textboxes, and one listbox. I want info from textbox1 to populate into the column 2 cell, textbox2 into column 3 cell, and the list box choice made into the column 4 cell.

I am doing this because I want to force anyone that tries to edit one of these columns to have to edit all 3.

I know my current code is fairly terrible and incomplete, please be kind!

 

[Micron]

So what part do you need help with? Seems like would be opening the form based on 1 or more of 3 columns (say B,C,D).

Dim rng As Range
Set rng = Intersect(Target, Range("B:D"))
If Not rng Is Nothing Then 'if user changes cells in any other columns, nothing should happen

I take it that your code is using the worksheet change event.
If that's not what you're asking about, then perhaps clarify and post your code (please use code tags - vba button on posting toolbar)

 

[Diabentes]

So what part do you need help with? Seems like would be opening the form based on 1 or more of 3 columns (say B,C,D).

Dim rng As Range
Set rng = Intersect(Target, Range("B:D"))
If Not rng Is Nothing Then 'if user changes cells in any other columns, nothing should happen

I take it that your code is using the worksheet change event.
If that's not what you're asking about, then perhaps clarify and post your code (please use code tags - vba button

So what part do you need help with? Seems like would be opening the form based on 1 or more of 3 columns (say B,C,D).

Dim rng As Range
Set rng = Intersect(Target, Range("B:D"))
If Not rng Is Nothing Then 'if user changes cells in any other columns, nothing should happen

I take it that your code is using the worksheet change event.
If that's not what you're asking about, then perhaps clarify and post your code (please use code tags - vba button on posting toolbar)

Hi sorry, thank you very much for your reply. thought I had uploaded pictures of the code. Mainly the starting part I am really struggling. I’m not sure with this way of making the form open from any of the 3 columns, how to link the user form fields to be placed into the cells. I am indeed using the worksheet change event.

 

[Diabentes]

Afternoon all,

I have some code. Basically, if someone tries to edit column 4, a user form should appear. The user form has 2 text boxes and 1 list box. They input the data, click the update button and the data populates into 3 separate cells. There is also a cancel button that closes the form down, and a reset button that wipes the current selections on the form to restart.

Worksheet change coding:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 4 Then

Set statuscell = Target

Set date1cell = Target.Offset(0, -2)

Set date2cell = Target.Offset(0, -1)

UserForm2.Show

End If

End Sub

Userform code:

Private Sub CommandButton1_Click()

Unload Me

End Sub

Private Sub CommandButton2_Click()

Unload Me
 
UserForm2.Show

End Sub

Private Sub CommandButton3_Click()

statuscell = ListBox1.Value

date1cell = TextBox1.Value

date2cell = TextBox2.Value

Unload Me

End Sub

Private Sub UserForm_Initialize()

ListBox1.AddItem "Pass"

ListBox1.AddItem "Fail"

ListBox1.AddItem "Repeat"

ListBox.AddItem "N/A"

End Sub

Module code:

Option Explicit

Public date1cell As Range

Public date2cell As Range

Public statuscell As Range

I don't understand what the problem is. Any thoughts on how to fix would be greatly appreciated, and if you're feeling brave, how to do the exact same thing but if someone tries to edit any of 3 different columns.

Cheers,

Ben.

 

[Fluff]

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.
This time I have merged both threads.