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" 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.
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 wrote: > 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" 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
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 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 wrote: > >> 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" 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. >> >> NO