Userforms again !

[rollo]

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!

 

[Colo]

Hi rollo, So let me know which part you don't understand?

 

[rollo]

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]

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]

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

rollo

 

[rollo]

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]

Did you miss the first part of Colo's 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]

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]

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.

 

[PaddyD]

"I can't believe the OP hasn't activated the sheet at some stage"