• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

UserForm failed when Workbook is xlMinimized

sgmpatnaik

sgmpatnaik

Active Member
Hello


i am try to do a new data entry type that is when i open the excel file a UserForm will display and it is fine for me but there is one problem that is when i enter the data with UserForm then i got one error msg kindly suggest me which line i forget to add


in the workbook module i gave the below code

[pre]
Code: 
Private Sub Workbook_Open()
Dim wb As Workbook
For Each wb In Application.Workbooks
wb.Windows(1).WindowState = xlMinimized
'wb.Windows(1).WindowState = xlMinimized
Next
ThisWorkbook.Windows(1).Visible = False
GateWay.Show vbModeless
End Sub

in the UserForm i gave the below module

[pre][code]Private Sub CommandButton1_Click()
Dim nextrow As Integer
'=COUNTA (B:B)+4
nextrow = WorksheetFunction.CountA(Sheets("Dispatch Register").Range("B:B")) + 4
Sheets("Dispatch Register").Unprotect Password:="1243" 'replase password with actual password
Sheets("Dispatch Register").Cells(nextrow, 2) = Dispatch_Register.TextBox1.Value
Sheets("Dispatch Register").Cells(nextrow, 4) = Dispatch_Register.ComboBox1.Value
Sheets("Dispatch Register").Cells(nextrow, 5) = Dispatch_Register.TextBox2.Value
Sheets("Dispatch Register").Cells(nextrow, 6) = Dispatch_Register.TextBox3.Value
Sheets("Dispatch Register").Cells(nextrow, 7) = Dispatch_Register.TextBox4.Value
Sheets("Dispatch Register").Cells(nextrow, 8) = Dispatch_Register.TextBox5.Value
Sheets("Dispatch Register").Cells(nextrow, 9) = Dispatch_Register.TextBox6.Value
'Sheets("Dispatch Register").Cells(nextrow, 11) = Dispatch_Register.TextBox7.Value
Sheets("Dispatch Register").Cells(nextrow, 12) = Dispatch_Register.ComboBox2.Value
Sheets("Dispatch Register").Cells(nextrow, 10) = Dispatch_Register.TextBox7.Value
Sheets("Dispatch Register").Cells(nextrow, 13) = Dispatch_Register.TextBox8.Value
Sheets("Dispatch Register").Cells(nextrow, 15) = Dispatch_Register.ComboBox3.Value

Sheets("Dispatch Register").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingHyperlinks:=True, AllowFiltering _
:=True, AllowUsingPivotTables:=True, Password:="1243" 'replace password with actual password

'Clear The Text
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
ComboBox1.Value = ""
TextBox7.Value = ""
ComboBox2.Value = ""
TextBox8.Value = ""
ComboBox3.Value = ""

End Sub
[/pre]

i got the error MSG for the first line as


Run-time error1004

Method 'sheets' of object_'Global'failed


and when i press the debug button then a yellow line is display for the first line that is

nextrow = WorksheetFunction.CountA(Sheets("Dispatch Register").Range("B:B")) + 4[/code][/pre]
Kindly suggest me what is the wrong i have done


Thanks


SP
 
SP

The Counta function expects a valid worksheet name in the format that you see in a worksheet not in the format that you use/see in VBA


eg:

nextrow = WorksheetFunction.CountA('Dispatch Register'!$B:$B) + 4

or

myRng="'Dispatch Register'!$B:$B"

nextrow = WorksheetFunction.CountA(myRng) + 4
 
@Hui sir


sorry to inform you that i face some problem to built the code can you please provide the code for the UserForm


Thanks


SP
 
I think the issue lies with making the window invisible. Excel can't find the worksheet you are pointing to.


Why do you want to hide that workbook in the first place? Is it necessary? If not then use code like:

[pre]
Code: 
Private Sub Workbook_Open()
Dim wb As Workbook
For Each wb In Application.Workbooks
wb.Windows(1).WindowState = xlMinimized
'wb.Windows(1).WindowState = xlMinimized
Next
GateWay.Show vbModeless
End Sub[/pre]

Or otherwise qualify the worksheets completely like below everywhere.

[code]Sheets("Dispatch Register").Cells(nextrow, 2) = Dispatch_Register.TextBox1.Value

with appropriate workbook reference

Thisworkbook.Sheets("Dispatch Register").Cells(nextrow, 2) = Dispatch_Register.TextBox1.Value[/code]
 
@SirJB7


Hello Sir,


How r u & how is your Health now


thanks for your replay please download the file from below mention link


https://dl.dropboxusercontent.com/u/75654703/Sample%20Book.xlsm


Thanks


SP
 
Hi, sgmpatnaik!


I'm fine, thank you very much for your concern, I appreciate.


I did this:

- opened your workbook in Excel, got the error

- added a condition for excluding it for the actual workbook (If wb.Name <> ThisWorkbook.Name Then ...) and it worked

- not happy with the solution, I saved the workbook, closed Excel, opened the workbook and worked fine

- restore the open event code as original (delete the If added previously), saved & closed, reopened and... it worked


I could never repeat the error again, not even having downloaded again the file from the posted link. And what is worse is that I don't why it worked wrong the first time, and after that well with and without the exclusion for the actual workbook.


Sorry but this is what happened. Hope it helps, even if it's not fully logical.


Regards!


PS: Before sending this comment I tried another thing:

- closed Excel, deleted saved copies of your workbook, deleted downloaded copies in Temp folder

- downloaded again from your dropbox, opened in Excel, got the error

- gone to the VBA editor, finished running the macro, Excel screen was not visible

- at the immediate window typed "ThisWorkbook.Windows(1).Visible = True"

- saved the file in the same temp folder as a copy, closed Excel, reopened the saved file... and worked.
 
@SirJB7


Thank Q Sir for your guide lines, i tried as per your solution and it's working


thanks once again


@Deb


Hi i tried your solution also and it's worked when i change the lines as


ThisWorkbook.Sheets("SheetName")


Thank you All


Thanks


SP
 
Hi, sgmpatnaik!

Glad you solved it, but this time I should recognize that it was a blind shot.

Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
Hi, sgmpatnaik!

@Deb

Hi i tried your solution also and it's worked when i change the lines as

ThisWorkbook.Sheets("SheetName")
Click to expand...

Thanks... for confirming.. that my IMAGINED code, which was provided by MIND READER Shri.. is working for you.. :)


Regards!

Deb
 
@Deb


Hi


sorry Shri actually the words are going to you


Thanks


@Deb


ya Deb you are correct and last night i was in party and may be the party effect is still one me, any how thanks for your compliments


Thanks


SP
 
You must log in or register to reply here.
Back
Top