Userforms again !

rollo

New Member
Joined
Feb 15, 2002
Messages
43
Have already checked through 200+ previous posts but cannot find the info that I need.
I have a worksheet which has existing data in columns A & B. I need to set up a userform which will display the existing data from columns A & B, and then allow a user to select Yes or No from a dropdown list (or similar), with the response going into column C

A.................B...............C
Dept............Number........Yes or No
Dispatch..........1
Accounts..........0
Export............1
Supplies..........1
Journal...........0

The userform needs to appear when the user opens the worksheet. This userform needs to be set up so that the user will see the existing data in each of the cells in Col A & B (first to last cell). Users must make a response of Yes or No before the next records are displayed (if no response is made, ideally a message should appear asking them to enter a response).
The populated cells in cols A & B can be variable.
Finally, a user must not be allowed to close the worksheet if no response has been made to any of the corresponding cells in col C.

Would greatly appreciate any help ……. many thanks!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
Hi rollo, So let me know which part you don't understand?
 

rollo

New Member
Joined
Feb 15, 2002
Messages
43
Thanks for your post, Colo
The problem that I am having is how to set up a userform that will show the info from columns A & B, and allow a user to select Yes/No from C. It is also important for users not to be allowed to exit from the worksheet until they have completed all entries in column C.
Would appreciate any help you could give.
Many thanks - rollo
 

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
Hi rollo, I assume as follows.

*Userform name as UserForm1
And This userform has
Label1 for display Data of Column A
Label2 for display Data of Column B
ComboBox1 for select Yes or No
CommandButton1 for goto next data. (Caption is "Next Data")

*Sheet name as Sheet1

<PRE><FONT color=#666699>'Put this code on Sheet1 module---------------------------------------------------
</FONT>
<FONT color=red>Private <FONT color=red>Sub </FONT></FONT>Worksheet_Activate()

UserForm1.Show

<FONT color=red>End Sub</FONT>



<FONT color=#666699>'Put this code on std module ---------------------------------------------------
</FONT>
<FONT color=red>Public </FONT>Declare <FONT color=red>Function </FONT>FindWindow Lib "user32" Alias "FindWindowA" _

(<FONT color=red>ByVal</FONT> lpClassName <FONT color=red>As</FONT><FONT color=red> String</FONT>, <FONT color=red>ByVal</FONT> lpWindowName <FONT color=red>As</FONT><FONT color=red> String</FONT>) <FONT color=red>As</FONT><FONT color=red> Long</FONT>

<FONT color=red>Public </FONT>Declare <FONT color=red>Function </FONT>GetWindowLong Lib "user32" Alias "GetWindowLongA" _

(<FONT color=red>ByVal</FONT> hWnd <FONT color=red>As</FONT><FONT color=red> Long</FONT>, <FONT color=red>ByVal</FONT> nIndex <FONT color=red>As</FONT><FONT color=red> Long</FONT>) <FONT color=red>As</FONT><FONT color=red> Long</FONT>

<FONT color=red>Public </FONT>Declare <FONT color=red>Function </FONT>SetWindowLong Lib "user32" Alias "SetWindowLongA" _

(<FONT color=red>ByVal</FONT> hWnd <FONT color=red>As</FONT><FONT color=red> Long</FONT>, <FONT color=red>ByVal</FONT> nIndex <FONT color=red>As</FONT><FONT color=red> Long</FONT>, <FONT color=red>ByVal</FONT> dwNewLong <FONT color=red>As</FONT><FONT color=red> Long</FONT>) <FONT color=red>As</FONT><FONT color=red> Long</FONT>

<FONT color=red>Public </FONT>Declare <FONT color=red>Function </FONT>DrawMenuBar Lib "user32" (<FONT color=red>ByVal</FONT> hWnd <FONT color=red>As</FONT><FONT color=red> Long</FONT>) <FONT color=red>As</FONT><FONT color=red> Long</FONT>

<FONT color=red>Public </FONT><FONT color=red>Const </FONT>GWL_STYLE <FONT color=red>As</FONT><FONT color=red> Long</FONT> = -16&

<FONT color=red>Public </FONT><FONT color=red>Const </FONT>WS_SYSMENU <FONT color=red>As</FONT><FONT color=red> Long</FONT> = &H80000



<FONT color=red>Public </FONT><FONT color=red>Sub </FONT>DisableButton(<FONT color=red>ByVal</FONT> strFrmCpt <FONT color=red>As</FONT><FONT color=red> String</FONT>)

<FONT color=#666699>'Disable x button of userform
</FONT>
<FONT color=red>Dim </FONT>hWnd <FONT color=red>As</FONT><FONT color=red> Long</FONT>

<FONT color=red>Dim </FONT>lngWstyle <FONT color=red>As</FONT><FONT color=red> Long</FONT>

hWnd = FindWindow(vbNullString, strFrmCpt)

lngWstyle = GetWindowLong(hWnd, GWL_STYLE)

SetWindowLong hWnd, GWL_STYLE, lngWstyle And (Not WS_SYSMENU)

DrawMenuBar hWnd

<FONT color=red>End Sub</FONT>





<FONT color=#666699>'Put this code on UserForm module-----------------------------------------------------
</FONT>
<FONT color=red>Private <FONT color=red>Sub </FONT></FONT>UserForm_Initialize()

<FONT color=#666699> 'Disable X button
</FONT>
<FONT color=red>Call</FONT> DisableButton(Me.Caption)

<FONT color=#666699> 'Set list to ComboBox1
</FONT>
ComboBox1.List = Array("YES", "NO")

<FONT color=#666699> 'Disable CommandButton1 for avoid user's click
</FONT>
CommandButton1.Enabled =<FONT color=red> False</FONT>

<FONT color=#666699> 'Set counter into CommandButton1 tag
</FONT>
Me.CommandButton1.Tag = 0

<FONT color=#666699> 'Set Data to Label
</FONT>
<FONT color=red>Call</FONT> SetDataToLabel

<FONT color=red>End Sub</FONT>



<FONT color=red>Private <FONT color=red>Sub </FONT></FONT>ComboBox1_Change()

<FONT color=red>If </FONT>ComboBox1.ListIndex <> -1 Then

CommandButton1.Enabled =<FONT color=red> True</FONT>

<FONT color=red>End If</FONT>

<FONT color=red>End Sub</FONT>



<FONT color=red>Private <FONT color=red>Sub </FONT></FONT>CommandButton1_Click()

<FONT color=red>Dim </FONT>lngOffset <FONT color=red>As</FONT><FONT color=red> Long</FONT>

lngOffset = Val(CommandButton1.Tag)

[C2].Offset(lngOffset).Value = ComboBox1.Value

CommandButton1.Tag = lngOffset + 1

<FONT color=red>If </FONT>[A65536].End(xlUp).Row - 2 = lngOffset Then

Unload Me

MsgBox "All data has been inputed"

<FONT color=red>End If</FONT>

<FONT color=red>Call</FONT> SetDataToLabel

ComboBox1.ListIndex = -1

CommandButton1.Enabled =<FONT color=red> False</FONT>

<FONT color=red>End Sub</FONT>



<FONT color=red>Private <FONT color=red>Sub </FONT></FONT>SetDataToLabel()

<FONT color=#666699>'For commn procedure
</FONT>
<FONT color=red>Dim </FONT>lngOffset <FONT color=red>As</FONT><FONT color=red> Long</FONT>

lngOffset = Val(CommandButton1.Tag)

Label1.Caption = [A2].Offset(lngOffset).Value

Label2.Caption = [B2].Offset(lngOffset).Value

<FONT color=red>End Sub</FONT>


</PRE>
 

rollo

New Member
Joined
Feb 15, 2002
Messages
43

ADVERTISEMENT

Wow ......... many, many thanks, Colo!!! Will try it out - don't go too far away!!!!

rollo
 

rollo

New Member
Joined
Feb 15, 2002
Messages
43
Colo ...... its me again.
So far the userform is brilliant - just two problems which I can't seem to resolve.
The userform needs to open automatically when the worksheet is opened - at the moment I have to use ALT+F11 and open in the VB Editor.
Similarly, when all the inputting is complete, closing the userform takes you into the VB Editor again, rather than closing the worksheet which is what I need.
If you have the time to have a look at these last two problems I would be extremely grateful.
Many thanks ....... rollo
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Did you miss the first part of Colo's code?

Code:
Private Sub Worksheet_Activate()
 UserForm1.Show
End Sub

This should be put in the module for the sheet which contains your data (not in a general module). Then the user form will be shown automatically when the user activates the sheet.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Might remarks like "than closing the worksheet which is what I need" imply that the OP means the workbook, not the worksheet?

Paddy
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Paddy,

Yes I thought that too, but decided to take it one step at a time. I can't believe the OP hasn't activated the sheet at some stage.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

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