Default Text for Textbox of Userform

Userform utility of VBA has always fascinated me to experiments beyond the limits. Well, I haven't touched the max yet. but for me, it's always necessary to dig new possibilities from available resources.

Today I have tried very common experiments with "Textbox Tool" in Userform.

I'm working on some serious Login system for the VBA Applications which will authenticate the user's credentials by matching it from the external database(Google Sheet). If it matched with the source strings, the program will allow the user to access the remaining features of the application. This Login system will be required the active internet connection.

But, that is not the subject of the discussion here.

We have already seen on many websites and on applications, the default text imprinted on the textboxes. Let's say, you are on the login page of any website. In the Username Box, you could see the default text like "USERNAME" or "TYPE YOUR USER ID HERE" etc....

And When you click the textbox, the default text will be disappeared and textbox gets blank and it will allow the user to write their user id. And if you left the textbox empty, it will again show the Default text on the box.

So How we can code the same in Excel VBA Textboxes?

I have used two events of Textbox

1. Enter Event - The Enter event occurs before a control actually receives the focus from a control on the same form or report.
2. AfterUpdate Event - The AfterUpdate event occurs after changed data in a control or record is updated.

--------- 

Private Sub UserForm_Initialize()

     'Assigning the DEFAULT values to the Textboxes
     UserForm1.TextBox1.Value = "USERNAME"

     'the Forecolor of the Textbox when userform initializes
     UserForm1.TextBox1.ForeColor = &HC0C0C0 'light gray

End Sub

---------

Private Sub TextBox1_Enter()

     UserForm1.TextBox1.Value = ""
     UserForm1.TextBox1.ForeColor = &H0& 'black

End Sub

---------

Private Sub TextBox1_AfterUpdate()

     'AFTER UPDATING THE TEXTBOX IF THERE'S NO STRING
     If UserForm1.TextBox1.Value = "" Then
          UserForm1.TextBox1.Value = "USERNAME"
          UserForm1.TextBox1.ForeColor = &HC0C0C0
     End If

End Sub

----------

It's work like Pro! Lol...

Try urself with this code

Thank you

- Kamal Bharakhda

How to start Excel VBA Programming ?

Question:

Hi All! I considered myself an advanced user until I learned there was an entire world of VBA out there. So now I’m trying to teach myself. This group is an amazing resource. Thanks to all that contribute!

Question: Does anyone have a specific method or process in planning an Excel project?

I’m finding there’s so much you can do and I end up getting lost down rabbit holes. I decided to start with designing the visual aspect of UserForms first then figuring out how to make them work later. I’m not sure that is the best way.

Hopefully, my question makes sense. Thanks for any and all feedback!

===========

Answer:

Let's begin with the home.. :)
Before a Six month ago, I was in your place. But, I have decided to learn VBA anyhow. I'm an Engineer BUT, I was too much far from the world of programming.
I started learning VBA by Recording A Macro. Yes,
there are two features available in Microsoft Excel to do an automation task.
I used to record macros and see how it coded. I took little help from my cousin who knows VBA programming. He challenged me with the little tasks.
task Like,
Copy every time the cell value of A1 of sheet1 to Cell A1 of sheet2
So, that's how it begins for me.
We are all for you as my Cousin brother was for me!
And one more important point to remember.
whenever you feel just little in the amount that, you can write little codes on your own. Then go for the bigger project to develop. and challenge your self to develop each and every idea of yours regarding your dreaming application.

1. First, design your idea.
2. Write down the functionality of your dream application.
3. Make the design on paper that, how it will look
4. Make the design on paper that, how it will work.
5 Finally, think about how I will code this damn!

And we are always here for all of our group members.
thank you and all the best.

Enter Query in Column(A) and get the result in Column(B) by Lookup function using VBA

Question :

Please, you can help me with one VBA Code which is able to extract from two columns A, B....B value. But how?

If I have database în sheet 2:

(col.A) 1. Jhon..... 30 years old(col. B)
(col.A) 2. James..... 32 years old(col. B)
(col.A) 3. Bob..... 35 years old(col. B)
.............................................................
(col.A) n. John..... 39 years old(col. B)

And I want în sheet 1:

When I type în col.A for ex: "James", în col.B gives "32 years old".....then aleatory typed "John" în col.A and în col B give "39 years old" etc.

Can you help me with that VBA Code?

Thank you,

Solution: 

Let's first making our Database Dynamic.

Sheets("date") has the data which you want to look into for your query.

1. Go to Ribbon --> Formulas --> Name Manager --> Click on button NEW
2. in New box, you are asking for new range details.
3. The name is as "Database"
4. Now, in "referes to": paste the following formula and save it and close that.

=OFFSET(date!$A$2,0,0,COUNTA(date!$A:$A)-1,2)

5. now, open VBA Compiler
6. Open Project Window
7. under Microsoft Excel Objects find the Sheet where you want to insert the query and get the result in the same.

In your case, Sheets("vlookup") is your query sheet.

8. Click on Sheet1(vlookup) and the coding area will be open up.
9. Now you want to change the value automatically after entering the value in the specific cell, So, in this case, we required "Worksheet_Change" procedure to fire up the result.
10. Select the Worksheet Object and then select the "Change" Procedure
11. You will get the,

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
Now insert the following code in between above two lines.
=======================
If Target.Column = 1 Then
Application.EnableEvents = False
With THEVL.Sheets("vlookup")

.Activate
.Select
.Cells(Target.Row, 2).Value = "=IFERROR(VLOOKUP(A" & Target.Row & ",Database_v,2,FALSE),0)"
End With
Application.EnableEvents = True

End 

========================

==

12. Save the Excel and restart the application.
13. Try to add something to anywhere in Column(A) of a sheet("vlookup"). you will get the result in adjacent to the cell.

I hope it helps you and other folks.

Thank you.
- Kamal Bharakhda

INDIAN CURRENCY FORMAT FOR VBA EXCEL

Hello Mates, 

When you work with Userform in Excel VBA, usually sometimes you need to retrieve or reflect currency figure in the Userform (Like in Textbox, in Label or in Listbox) from the range(s) of the workbook. 

When you directly assign values to the objects, Userform generally takes it as a number and never reflect the proper format that you required in as Indian or else country's currency's format. 

So, I have struggled a lot to find the solution indeed. After a constantly looking for the solution of this, I came across to the following worksheet function. 

It's ".Text" formula

Yes, here's the Syntax:

userform1.textbox1.value = Application.WorksheetFunction.Text(VariableName.value, "[>=10000000]##\,##\,##\,##0.0#;[>=100000] ##\,##\,##0.0#;##,##0.0#")

Thank you. 

- Kamal Bharakhda

Select Column data dynamically

I want to share my best work for the people who have a passion for Microsoft Excel.

I especially focussed on the VBA MACRO based Applications in MS EXCEL for,

Automation of Reports
Applications like sale purchase invoice printing. Etc...

Today, I want to share my first coding in VBA to all.

Sometimes you need to select dynamically the whole data from the column to copy them automatically in anywhere. Maybe sometimes it happens when your worksheet went full of data or filled partially but you need to select data of any column automatically till it's the last cell then?

I have done the coding for that task. Here it is. Just paste it into your VBA module.

Code:

Sub Test_Module_1()
'By Kamal Bharakhda
Sheets("Assignment No. 1").Activate
Columns("B").Select
A = Selection.Rows.Count
LastCellofSheet = ActiveSheet.Cells(A, 2).Value
MsgBox "WorkSheet's Last Cell Value is" & " = " & LastCellofSheet
If LastCellofSheet = 0 Then
MsgBox "Worksheet's Last Row!" & " is = " & A
ActiveSheet.Cells(A, 2).End(xlUp).Select
B = ActiveCell.Row
C = ActiveCell.Column
MsgBox "Last Row of Column's Data" & " is = " & B
Range("B1", Cells(B, C)).Select
End If
End Sub


By Kamal Bharakhda