VBA Questions For Auto filling Cells

[data808]

I am using this and it works fine:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("E:E"), .Cells) Is Nothing Then
        Application.EnableEvents = False
        With Range("K" & Target.Row)
                ActiveSheet.Unprotect Password:="hello"
                .Value = "TEST"
        With Range("I" & Target.Row)
                .Value = "Stuff"
                ActiveSheet.Protect Password:="hello"
        End With
        End With
        Application.EnableEvents = True
        End If
    End With
End Sub

But now I want to add some if statements to make the user have to type in specific codes for it to trigger. Right now as long as they type anything into column E, it will trigger column I and K to auto-fill "TEST" and "Stuff".

How would I change this VBA so that if the user types "1234" into column E for example, only then will it trigger to auto-fill column I and K?

I would also like it if the user types anything other than "1234" or deletes it to leave column E blank, then columns I and K, for that row, will go back to being null or blank.

Thank you for your help on this.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[data808]

You're welcome. Thanks for the follow-up.

Sorry, how would I achieve this result with the VBA code and formula you provided, if the cells are merged?

Row 14 is where I would begin typing data values into O14 which is merged with cells P14, Q14, and R14. So A14, which is merged with B14, would be where I use your formula:

=IF(O14="","",TEXT(COUNTA(O$14:O14),"00"))

for the counter but it's not working. The VBA code is also not functional (probably because the cells are merged. Fyi, row 15 is different and would not be used for entering data because I have it skip a row in between items entered. So O16 is where I would enter item number 2. I also have merged cells for the rest of the auto-fill cells like how I had for columns K and I too but if you show me an example of the VBA code for merged cells on one of them, I can apply it to the rest.

Also not sure if you need this info either but above row 14 is all the header of the form with categories and titles so most of it won't be edited at any point except for the date field which I will enter the date into cell AY6 which is merged with row 7 and goes from AY-BH. Sorry if this is confusing but I basically made this form have very small cells so that I could merge what I needed to get the spacing correct for printing. I took a PDF file, which I am not good at creating in Adobe and tried to make an identical copy of it on an excel spreadsheet with these auto-fill features that the PDF did not have. Plus, I was not the author for the PDF so I can't unlock it to try and mess around with it to implement auto-fill features. The PDF is locked by someone that is no longer here.

 

[Peter_SSs]

• Merged cell and vba generally do not agree with each other. However, it may still be possible to achieve what you want.
  
  
• To be sure though, and to test, could we have an XL2BB sample of the worksheet from say rows 11:25 and columns A:T so that we can see exactly what is merged and what is not, as well as any formulas that you have (eg column A)?

 

[data808]

• Merged cell and vba generally do not agree with each other. However, it may still be possible to achieve what you want.
  
  
• To be sure though, and to test, could we have an XL2BB sample of the worksheet from say rows 11:25 and columns A:T so that we can see exactly what is merged and what is not, as well as any formulas that you have (eg column A)?

Maybe this might help? The yellow cells are the only cells that I want the user to type into. They will type in a numeric code that will be 3-4 digits (usually 4) and all the other cells with numbers and letters will auto fill based on the code they type in. The VBA that you provided works if the cells are not merged but here you can see the cells are merged. Also to the far left for column A which is merged with column B for where I want it to count how many items are listed has your formula that you also provided.

 

[Peter_SSs]

Maybe this might help?

Unfortunately we cannot copy from that and it would take a lot of manual setting up. Any reason that you cannot use XL2BB to give us the same thing (only need say columns A:Z)?

 

[data808]

Unfortunately we cannot copy from that and it would take a lot of manual setting up. Any reason that you cannot use XL2BB to give us the same thing (only need say columns A:Z)?

I can try. I never did that XL2BB before. Let me take a look.

 

[data808]

Unfortunately we cannot copy from that and it would take a lot of manual setting up. Any reason that you cannot use XL2BB to give us the same thing (only need say columns A:Z)?

Looks like I have to install add-ons or something when I looked at the XL2BB link in your post.

It had instructions and I don't think I want to install anything on a work computer if possible. Is there any other way?

 

[Peter_SSs]

Is there any other way?

Another option is to upload your sample file somewhere (eg DropBox, OneDrive, Google Drive etc) and provide a publicly shared link here. However, you may find that some helpers here choose not to download files from other sites or due to security restrictions at workplaces, are unable to download such files.

 

[data808]

Another option is to upload your sample file somewhere (eg DropBox, OneDrive, Google Drive etc) and provide a publicly shared link here. However, you may find that some helpers here choose not to download files from other sites or due to security restrictions at workplaces, are unable to download such files.

Instead I've come up with another way to get around it. I am going to record a macro to copy and paste the data that we worked on which functions properly when the cells are not merged. When the macro is run this, it should copy and paste from the unmerged cells on one sheet, then paste the values into the sheet that has merged cells.

Originally, what I wanted to do was just put something like this in the cell that I wanted to pull the value into:

=Sheet1!A6

The cell that I am inserting this formula into is the merged cell A14:B14 but it's not working. I'm thinking its probably cause the cell is merged but I'm not sure.

So this is why I came up with the recording a macro idea to copy and paste. But if you can give me a solution to make a merged cell equal an unmerged cell, I'd appreciate it.

Thanks.

 

[Peter_SSs]

Originally, what I wanted to do was just put something like this in the cell that I wanted to pull the value into:

=Sheet1!A6

The cell that I am inserting this formula into is the merged cell A14:B14 but it's not working.

Unless I have misunderstood what you are saying, there should be no issue at all with a structure like that. Here it is working ..

data808.xlsm
	A	B
4
5
6	abc 123
7
8
Sheet1

data808.xlsm
	A	B
12
13
14	abc 123
15
16
Sheet2

Cell Formulas
Range	Formula
A14	A14	=Sheet1!A6