Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Premium Excel Forms Course Conditional Formatting and Macro Lookup Question

0

I have 2 questions with the Premium Course which I dont seem to be able to resolve.

Question 1 refers to Conditional formatting. I have more than one cell on one line, although each cell is named seperatley the cells on the line act in cohoots with each other and either show and error when there is data entered into only one of them, or dont show (highlight) when no data is entered although the Validation shows the correct values. 

Question 2 refers to Macro 3 which concerns returning information to the form when searching the Entry (data) sheet when i wish to find a record, I get an Error coding and the Macro does not run.

I am well out of my depth with the course so I am trying to follow it to the letter. 

Assistance would be appreciated, Thanks in advance 

Post Edited
Title: Title was not descriptive.
Answer
Discuss

Discussion

Hi Jim, I'm taking a look at it now. Also, please use a descriptive title for forum questions, this way it helps future readers better find solutions to their issues :)
don (rep: 1989) Jan 16, '19 at 4:35 pm
Don would you like me to split the question into 2 and your answer into 2, I understand where you are coming from with your request.
Jim W (rep: 14) Jan 17, '19 at 7:08 am
No worries, this question was fine!
don (rep: 1989) Jan 17, '19 at 9:51 am
Oh, I forgot to mention, take note that the form password is visible in your file. I don't know if this is a big issue for you or not, but wanted to mention it.
don (rep: 1989) Jan 17, '19 at 9:53 am
Yes I know Don, I left there so you could access the Form, I should have realized that you would know a way round. It has been removed now. by deleting the attached file and re adding the updated version. Validation all done, Thanks a lot for the help, much appreciated.
Jim W (rep: 14) Jan 17, '19 at 10:03 am
No worries, you're welcome :)
don (rep: 1989) Jan 17, '19 at 7:42 pm
Add to Discussion

Answers

1
Selected Answer

First, make a copy of your current file in case this messes things up more - as you have a large form, things can get messy.

That said, it looks like you have a great organizational structure for the Validation and Formatting columns

Conditional Formatting

This you just need to start over; your conditional formatting assumes that everything is in the same column, but, you have 2 input columns in your form and, given the way your data is setup, you cannot just copy the conditional formatting down like it was done in the premium course.

For instance, Name1 and Name2 are in different columns, but the formula that controls the Formatting display is all in one column, S11 & S12.

Go to Home tab > Conditional Formatting > Manage Rules > select the rule and click Delete Rule and hit OK.

Now, you will have to reapply the conditional formatting and, in your case, the easiest way to do this is by hand; this way, you will be sure that it is apply where it should apply.

Macro

You made a little mistake here with how to reference cells and ranges and this little mistake was the only error that I noticed.

Let's take formId as an example.

You set it like this:

Set formId = sourceSheet.Range("formIDNumber")

and then you referenced it like this:

sourceSheet.Range("formId").Value = dataSheet.Cells(recordRow, 2).Value

When you Set the variable, you already set it equal to the desired Range on the desired worksheet (sourceSheet), so, you should reference it like this:

formId.Value = dataSheet.Cells(recordRow, 2).Value

In the middle of the macro, where you Cleared the data, you can see that you did it correctly when you used it like this:

formId.Value = ""

Basically, you used the Range object variable that you created as if it were a named range within the worksheet.

Let me know if this doesn't make sense and I can try to explain it better.

Discuss

Discussion

Thanks for the advice, I have got Macro 3 working now, but not updating as yet. I have yet to redo the Validation but that will be done this afternoon. For info this Entry form will get twice as large as in our other Open Competitions we have 4 players. Plus I have yet to update Macro 2 with inputting the relevent data into the correct time slots on the start sheets. Jim
Jim W (rep: 14) Jan 17, '19 at 7:12 am
It seems like you're coming along well, it can be a rather arduous task to create a nice working form, especially one as large as yours. And you don't need to split this question up or anything like that, I just wanted you to use a descriptive title, thats all. All is good)
don (rep: 1989) Jan 17, '19 at 9:51 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login