Excel VBA Forms Part 3 - Adding Code to a Form

By Andrew Gould

[Kingco]

This guy knows a lot of concepts in VBA off-heart, and his memory never fails him to recall each one. Master 🙌. 🙇

[TheSardOz]

Best teacher on the net!!! Thanks Andrew!

[MsBazinga]

This video series is the bet tutorial ever! Thanks Andrew.

[jacjorjac]

Hi there, I am LEARNING SO MUCH and feeling like a champ thanks to you

[debbie]

This course is fantastic. Everything is explained so clearly. Thank you so much.

[stefannakov]

WiseOwl tutorials are the best I've ever seen!

[tayof]

Thanks so much. Very clear and easy to follow.

[osamaabdelqader]

i am using vba since 2011...i have good experience but i really added a lot of things to my experience by your amazing detailed videos...
keep on going. ...

[haroldmcbroom]

In 19:22 in the timeline, correct me if I'm wrong, but the (end) Down 1 to reach the bottom of the list, only works if there are more than one entries already in the list, if there are no entries, or just one, could place the cellpointer in the wrong position, so I always add a test:

Quick Solution
(if you know nothing is at row 65536 (max number of rows in a 2002 spreadsheet), now it's over 1 million)
'Replace column "b" below with whatever column you know the data will be in.
Range("b65536").Select: Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select '(Down 1)

Also, if anyone else uses this method of traversing cells through VBA, I found a workaround that will allow you to go past column 'Z' without the run-time error

OLD METHOD
Range(Chr(64 + activecell.column) & Activecell.row+1).select '<-- This method will not work past cols A..Z, ex. AA1, etc...

NEW METHOD (You can also use the Cells().select command

Dim TmpR, TmpC as long '(do not use integer, or anything past the data types range will generate a run-time error)
Dim TmpAdr as string

TmpR = activecell.row : TmpC = activecell.column
TmpAdr = "R" & (TmpR + 1) & "C" & TmpC
Application.Goto Reference:=TmpAdr

[dniezby]

So you don’t need to “format as table” before doing these adds?

[haroldmcbroom]

Question, if I may. Forms were basically designed to allow input into a spreadsheet, but suppose we want to have, and you can probably see where I'm going with this, ...a form called "Space Station Options" or "Station Options" for short, and I do not want to show the spreadsheet to the user, but instead retrieve information from the spreadsheet and display that into the form. I know how to do this, BUT, where I'm iffy, is when I need to update information in the spreadsheet, and then update the StationForm.textbutton.value = "New Value", what needs to take place. Some buttons, such as the "Option Button", must be initialized before the form is displayed, and I discovered that most other entities will not display unless the form is hidden. Not sure why, not sure how to do this, any help would be appreciated, if you can give a simple example. My "Station Options" include the "Banking Operations" (Deposit, withdraw, borrow, payback) and must show various information which may require updating as options are clicked. Later on there will be more options, such as buying a ship, buying fuel, and other cargo such as food and cargo to be sold at other stations. I'm turning a spreadsheet (1 million+ rows by 16k+ columns) should be enough to create a sandbox universe with planets, stations, and asteroids that have 118 resources found on the periodic table.

When I get near a cell containing a planet, I will have the option to DOCK, then the station form will load, I already have this part working, but as I said, updating the form as form items change, is where i need help.

[jeanettegraff]

Can you use this same concept with a "form"? I am not maintaining a continuous list, but creating a fill-in form for submitting expense reimbursements. How do I tell the "userform" to go to the first cell in the expense form, then "add" additional lines?

[rali]

Great video as usual. Thank you so much, I’m taking more of a keen interest in VBA thanks to you.

I have a question, I can’t get the inputted data to appear as currency rather than as text which causes errors. Please help

[akshays]

excellent work, sir. can we use text box to enter data in different languages.? say hindi or any other. if yes, then how?

[robhunter]

Andrew, how would you incorporate a line into the ADDTOLIST button code that would search out a specific criterion? (e.g you want to use your userform to register the dates that you revisit "Deadpool". Completing the form with "Deadpool" and the date viewed, you now want the addtolist button to insert the vieweddate into the row that contains Deadpool (but no other row).I assume it involves VLOOKUP but I'm stuck on the actual format of the code.

[melisuzy]

Is there a way that you can initialize a text box to say dd/mm/yy instead of today's date?

[MrvladivostokMr]

formating FIlmGross values: ActiveCell.Offset(0, 1).NumberFormat = "$#, ##0.00"

[RolandK]

Thanks for a really nice vids!
I have one prob, Excel takes GROSS amount as text... I cannot make form to treat it as number...
Referencing to the upper cell, does not help.
Any advise?
P.S.
(i have numbers (price) in collumn F)
ActiveCell.Offset(, 5).Value = CDec(ActiveCell.Offset(, 5).Value)
has helped me out...
but should it be like this???? Your example shows, excel catches numbers automatically without any conversion.

[bengoldfinch]

Love your videos, when I copied the film gross and the date to the cells it pasted it as text rather than a number and a date I used cdate and cdbl to fix it but I'm not sure why have I done something wrong on the userform txtboxes to tell it it's text, I'm also using a Mac which could be the issue

[adriandorland]

Hi I am using Excel 2016 and when I run the form and click the add to list command button I get an error code 1004 application-defined or object-defined error

If I take the range statement out it runs ok but keeps over writing the code in B2:B4 can you help?