Hiding/Unloading Userform

fbexel

New Member
Joined
Jun 3, 2012
Messages
5
Hello,

Introduction

I am new in VBA. I am currently working on a Clinical application for a Complete blood count. I created a userform (Differential count) that has textbox on it. The Clinical specialist (User) activate a command button that will show/open the UserForm(differential count). The user will then enter keys in the textbox which will then continuously automatically enter the results in the designated cell ("'G45") as the user look and identifies cells in the microscope/slide.

Sub Diff1TextBox_Change()
Range("G45") = Diff1TextBox.Text
End Sub

The results in Range ("G45") is then counted depending on the keys selected. In the example below, "k", "l", "m"will be counted and results are transferred to another cells.

=LEN(G45)-LEN(SUBSTITUTE(G45,"k",""))
=LEN(G45)-LEN(SUBSTITUTE(G45,"l",""))
=LEN(G45)-LEN(SUBSTITUTE(G45,"m",""))

The result will be seen by the user as:

Neutrophil (k) = 30
Lymphocyte (l) = 30
Monocyte (m) = 40

Total number of cells = 100


My Solution???? but don't know how:

Is there a way to deactivate/Unload/hide a Useform(Differential count) with an active textbox WHEN the TOTAL number of cells reached 100?

I was trying the (logical form)

IF total cell >100 Then
Unload Userform
MsgBox "Count Complete"
End IF


Solutions, feedback, inspiration and ideas are greatly appreciated.

Many thanks

Fbexcel.
confused.gif
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,302
Office Version
  1. 2010
Platform
  1. Windows
Code:
Sub Diff1TextBox_Change()
    Range("G45") = Diff1TextBox.Text
    If len(Diff1TextBox.Text)=100 then
        msgbox "finished"
        Unload Me
    end if
End Sub
 

fbexel

New Member
Joined
Jun 3, 2012
Messages
5
Hi diddi,
I tried and It work. It automatically unload the Userform once ("G45") = 100. It would like to automate it further. What I was trying to accomplish is not to limit "G45" to 100 but instead extract whatever the user input based on what letters they entered. (e.g. k, l, m) the number counted for each designated letter is place in another cells/range ("G14:G16") which were eventually totaled to ("G30"). The unload should be triggered once the sum in ("G30") > 100. Therefore if the user keyed letters beside "k,l,m" then USERFORM/Diff1TextBox still is active.

Thanks again.

FBexel
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,302
Office Version
  1. 2010
Platform
  1. Windows
your welcome...
 

fbexel

New Member
Joined
Jun 3, 2012
Messages
5
Diddi,

I believe I figured out the solution to my problem. I had to change Range ("G30") total count to a =TEXT() function form.

Sub Diff1TextBox_Change()

Range("G45") = Diff1TextBox.Text
If Range("G33") = 100 Then
MsgBox "Count Complete"
Unload Me
End If
End Sub

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,130,045
Messages
5,639,744
Members
417,108
Latest member
Thein Than

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top