Re: $$Excel-Macros$$ Run time error - Type mismatch
Hi Check reference is given to Microsoft Visual Basic for Applications Extensibility 5.x Thanks On Mon, Apr 28, 2014 at 5:50 AM, Sam Mathai Chacko samde...@gmail.comwrote: Ondrej, for that you'll have to enable programmatic access to VBA. Just search the internet how to do that, or post back. Someone will help you. Regards, Sam Mathai Chacko On Apr 27, 2014 7:49 PM, Ondrej o.pel...@gmail.com wrote: HI buddies there is an error in the last sequence of this code (bold), and I can not find the reason why please help Sub btn_vytvorit_Click() Dim ws As Worksheet Dim wsName As String, Name As String Dim MsgError As String Dim wsCopyFrom As Worksheet Set wsCopyFrom = Worksheets(vzorovy_zapis) Dim Msg As String Dim msgsmazat As String Dim riadky As Range Dim noveriadky As Range Dim leden As Range Set leden = Range(A17:AA62) MsgError = Zadaný list již existuje! Vyberte bunku s jiným kódem. Potvrďte End. 'generate ws If ActiveCell.Column 2 Then MsgBox (Nemáte označený kód zaměstnance) Sheets(db_kontakty).Activate Else If Not IsEmpty(ActiveCell) Then Name = ActiveCell.Value Set ws = Worksheets.Add ws.Move after:=Sheets(Sheets.Count) 'not to have two equal ws Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Name = Name Then MsgBox MsgError, vbOKOnly, Pozor On Error GoTo Chyba End If Next wks ws.Name = Name 'copy data from source wsCopyFrom.Columns(A:AG).Copy ws.Range(A1:AG1000).PasteSpecial (xlPasteAll) ws.Range(S2).Value = ws.Name 'end of creating ws+setup row heights Set riadky = wsCopyFrom.Range(A1:AG1000) Set noveriadky = ws.Range(A1:AG1000) With riadky For r = 1 To .Rows.Count noveriadky.Rows(r).RowHeight = .Rows(r).RowHeight Next r End With Msg = Vložte údaje MsgBox (Msg) Chyba: Sheets(db_kontakty).Activate End If End If Dim i As Integer Dim SrcCmod As VBIDE.CodeModule Dim DstCmod As VBIDE.CodeModule * Set SrcCmod = ActiveWorkbook.VBProject.VBComponents(wsCopyFrom).CodeModule* Set DstCmod = ActiveWorkbook.VBProject.VBComponents(ws).CodeModule For i = 1 To SrcCmod.CountOfLines DstCmod.InsertLines i, SrcCmod.Lines(i, 1) Next i End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem,
Re: $$Excel-Macros$$ Run time error - Type mismatch
Vaibhav, before giving a solution, it is always a kind thing to do to ensure you validate what you are saying. If the Applications Extensibility library reference wasn't added, the sub-routine wouldn't have even execute because the declaration in the following lines would have not compiled. Dim SrcCmod As VBIDE.CodeModule Dim DstCmod As VBIDE.CodeModule Thanks, Sam Mathai Chacko On Mon, Apr 28, 2014 at 1:30 PM, Vaibhav Joshi v...@vabs.in wrote: Hi Check reference is given to Microsoft Visual Basic for Applications Extensibility 5.x Thanks On Mon, Apr 28, 2014 at 5:50 AM, Sam Mathai Chacko samde...@gmail.comwrote: Ondrej, for that you'll have to enable programmatic access to VBA. Just search the internet how to do that, or post back. Someone will help you. Regards, Sam Mathai Chacko On Apr 27, 2014 7:49 PM, Ondrej o.pel...@gmail.com wrote: HI buddies there is an error in the last sequence of this code (bold), and I can not find the reason why please help Sub btn_vytvorit_Click() Dim ws As Worksheet Dim wsName As String, Name As String Dim MsgError As String Dim wsCopyFrom As Worksheet Set wsCopyFrom = Worksheets(vzorovy_zapis) Dim Msg As String Dim msgsmazat As String Dim riadky As Range Dim noveriadky As Range Dim leden As Range Set leden = Range(A17:AA62) MsgError = Zadaný list již existuje! Vyberte bunku s jiným kódem. Potvrďte End. 'generate ws If ActiveCell.Column 2 Then MsgBox (Nemáte označený kód zaměstnance) Sheets(db_kontakty).Activate Else If Not IsEmpty(ActiveCell) Then Name = ActiveCell.Value Set ws = Worksheets.Add ws.Move after:=Sheets(Sheets.Count) 'not to have two equal ws Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Name = Name Then MsgBox MsgError, vbOKOnly, Pozor On Error GoTo Chyba End If Next wks ws.Name = Name 'copy data from source wsCopyFrom.Columns(A:AG).Copy ws.Range(A1:AG1000).PasteSpecial (xlPasteAll) ws.Range(S2).Value = ws.Name 'end of creating ws+setup row heights Set riadky = wsCopyFrom.Range(A1:AG1000) Set noveriadky = ws.Range(A1:AG1000) With riadky For r = 1 To .Rows.Count noveriadky.Rows(r).RowHeight = .Rows(r).RowHeight Next r End With Msg = Vložte údaje MsgBox (Msg) Chyba: Sheets(db_kontakty).Activate End If End If Dim i As Integer Dim SrcCmod As VBIDE.CodeModule Dim DstCmod As VBIDE.CodeModule * Set SrcCmod = ActiveWorkbook.VBProject.VBComponents(wsCopyFrom).CodeModule* Set DstCmod = ActiveWorkbook.VBProject.VBComponents(ws).CodeModule For i = 1 To SrcCmod.CountOfLines DstCmod.InsertLines i, SrcCmod.Lines(i, 1) Next i End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to
Re: $$Excel-Macros$$ Run time error - Type mismatch
Ondrej, for that you'll have to enable programmatic access to VBA. Just search the internet how to do that, or post back. Someone will help you. Regards, Sam Mathai Chacko On Apr 27, 2014 7:49 PM, Ondrej o.pel...@gmail.com wrote: HI buddies there is an error in the last sequence of this code (bold), and I can not find the reason why please help Sub btn_vytvorit_Click() Dim ws As Worksheet Dim wsName As String, Name As String Dim MsgError As String Dim wsCopyFrom As Worksheet Set wsCopyFrom = Worksheets(vzorovy_zapis) Dim Msg As String Dim msgsmazat As String Dim riadky As Range Dim noveriadky As Range Dim leden As Range Set leden = Range(A17:AA62) MsgError = Zadaný list již existuje! Vyberte bunku s jiným kódem. Potvrďte End. 'generate ws If ActiveCell.Column 2 Then MsgBox (Nemáte označený kód zaměstnance) Sheets(db_kontakty).Activate Else If Not IsEmpty(ActiveCell) Then Name = ActiveCell.Value Set ws = Worksheets.Add ws.Move after:=Sheets(Sheets.Count) 'not to have two equal ws Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Name = Name Then MsgBox MsgError, vbOKOnly, Pozor On Error GoTo Chyba End If Next wks ws.Name = Name 'copy data from source wsCopyFrom.Columns(A:AG).Copy ws.Range(A1:AG1000).PasteSpecial (xlPasteAll) ws.Range(S2).Value = ws.Name 'end of creating ws+setup row heights Set riadky = wsCopyFrom.Range(A1:AG1000) Set noveriadky = ws.Range(A1:AG1000) With riadky For r = 1 To .Rows.Count noveriadky.Rows(r).RowHeight = .Rows(r).RowHeight Next r End With Msg = Vložte údaje MsgBox (Msg) Chyba: Sheets(db_kontakty).Activate End If End If Dim i As Integer Dim SrcCmod As VBIDE.CodeModule Dim DstCmod As VBIDE.CodeModule * Set SrcCmod = ActiveWorkbook.VBProject.VBComponents(wsCopyFrom).CodeModule* Set DstCmod = ActiveWorkbook.VBProject.VBComponents(ws).CodeModule For i = 1 To SrcCmod.CountOfLines DstCmod.InsertLines i, SrcCmod.Lines(i, 1) Next i End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
